IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = N'TransformAlertParameterDimProc')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[TransformAlertParameterDimProc] @WaterMark xml AS RETURN 1')
END
GO
DECLARE @Statement NVARCHAR(MAX)
SET @Statement =
N'ALTER PROCEDURE [dbo].[TransformAlertParameterDimProc](@WaterMark XML)
AS BEGIN
SET NOCOUNT ON
DECLARE @utc DATETIME = GETUTCDATE()
DECLARE @errorNumber INT, @errorSeverity INT, @errorState INT, @errorLine INT, @errorProcedure NVARCHAR(256), @errorMessage NVARCHAR(MAX), @task NVARCHAR(512)
DECLARE @BatchId INT, @Updated INT = 0, @Inserted INT = 0, @retval INT = -1, @err INT = 0, @startTranCount INT = @@TRANCOUNT, @source1WM DATETIME, @source1MaxWM DATETIME
DECLARE @rowsToProcess INT = 0, @rejectedRowCount INT = 0, @canContinue BIT = 1, @loopCount INT
DECLARE @isReconciled BIT = 0
SELECT @task = ''Inserting into Dimension''
INSERT INTO dbo.AlertParameterDimvw (
[SourceId],
[ParameterValue],
[InsertedBatchId],
[UpdatedBatchId],
[IsDeleted]
)
SELECT DISTINCT
alert.DatasourceId,
paramNodes.parameter.value(''.'', ''nvarchar(255)''),
@BatchId,
0,
0
FROM (SELECT DatasourceId, NEWID() AS ParamId, CAST(AlertParams AS XML) as AlertParams FROM DWTemp.TransformAlertParameterDimProc_Source1 WHERE AlertParams IS NOT NULL) AS alert
CROSS APPLY alert.AlertParams.nodes(''AlertParameters/*'') as paramNodes(parameter)
LEFT JOIN dbo.AlertParameterDimvw dest ON (paramNodes.parameter.value(''.'', ''nvarchar(255)'') = dest.ParameterValue)
WHERE dest.AlertParameterDimKey IS NULL