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