mardi 28 mars 2017

SQL Server IF ELSE INSERT NOT WORKING

I'm building a stored procedure in SQL Server with the following code:

USE [msdb]
GO
/****** Object:  StoredProcedure [dbo].[RollbackStgTableToPartition]    
Script Date: 28/03/2017 10:24:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[MergePartitionsCleanup]
@debug INT
AS
DECLARE @tsql NVARCHAR(4000)
,@MergeBoundaryPoint DATETIME2(0)

BEGIN
SET NOCOUNT ON;

SELECT @MergeBoundaryPoint = CAST(MIN(rv.value) AS DATETIME2(0))
    FROM sys.partition_functions  pf
    JOIN sys.partition_range_values rv ON pf.function_id=rv.function_id
    where pf.name='META_Standard_Invoice_PF';

IF (
    SELECT COUNT(*)
    FROM [StrsCollector_PROD].[dbo].[META_Standard_Invoice]
    WHERE [BlobInfo.CreationDateTime] < dateadd(MONTH, 1, @MergeBoundaryPoint)
) = 0
    BEGIN
        SET @tsql = 'ALTER PARTITION FUNCTION META_Standard_Invoice_PF () MERGE RANGE (''' + CAST(@MergeBoundaryPoint AS CHAR(10)) + ''')'

        INSERT INTO [msdb].[dbo].[PartitionMoveCommandLog] values (GETDATE(),@tsql,null)

        IF (@debug = 0)
            BEGIN
                EXEC (@tsql);
            END
    END
ELSE 
    BEGIN
        INSERT INTO [msdb].[dbo].[PartitionMoveCommandLog] values (GETDATE(),'ERROR: Records exist around boundary point ' + CAST(@MergeBoundaryPoint AS CHAR(10)) + '. Not merging.',null)
    END

SET @tsql = 'TRUNCATE TABLE [StrsCollector_PROD].[dbo].[META_Standard_Invoice_Staging]'

INSERT INTO [msdb].[dbo].[PartitionMoveCommandLog] values (GETDATE(),@tsql,null)

IF (@debug = 0)
    BEGIN
        EXEC(@tsql);
    END
END

The INSERT statement on the ELSE clause is inserting NULL instead of the string with the MergeBoudaryPoint variable value.

I've tried outside the stored procedure and works. I can spot the issue. Any thoughts?

Aucun commentaire:

Enregistrer un commentaire