SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO

IF EXISTS (SELECT *
FROM dbo.sysobjects
WHERE id = object_id(N'dbo.qryArtsExport_PartitionMaintenance') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.qryArtsExport_PartitionMaintenance
GO

CREATE PROCEDURE dbo.qryArtsExport_PartitionMaintenance
    @Now DATETIME = NULL,
    @PartitionsToKeep SMALLINT = 365
AS
SET NOCOUNT ON
BEGIN
    IF @Now IS NULL SET @Now = GETUTCDATE()

    DECLARE
        @BufferSize SMALLINT,
        @Sql NVARCHAR(4000),
        @P SMALLINT = DATEPART(DY, @Now),
        @I SMALLINT = 0

    SELECT
        @BufferSize = COUNT(*)
    FROM sys.partitions P
        JOIN sys.tables T ON P.object_id = T.object_id
    WHERE
        T.name = 'tbArtsExport'
        AND P.index_id = 1

    IF @PartitionsToKeep > @BufferSize
    BEGIN
        RAISERROR ('Can''t keep more partitions than the current buffer size of: %i', 16, 1, @BufferSize)
        RETURN
    END

    WHILE @I < @BufferSize - @PartitionsToKeep
    BEGIN
        SET @P = (@P + 1) % @BufferSize
        IF @P = 0 SET @P = @BufferSize
        /* Partition are 1 offset */

        SET @Sql  = 'ALTER TABLE tbArtsExport SWITCH PARTITION <p> TO tbArtsExport_SwitchTarget PARTITION <p>'
        SET @Sql = REPLACE(@Sql, '<p>', CAST(@P AS NVARCHAR))
        EXEC sp_executesql @Sql

        SET @I = @I + 1;
    END

    TRUNCATE TABLE tbArtsExport_SwitchTarget;
END
GO
