Pages

Thursday, October 24, 2013

SQLServer function to update the same table where cursor selects values

--Check whether cursor is open
IF CURSOR_STATUS('global','c4') >= 0
BEGIN
        CLOSE c4
        DEALLOCATE c4
END

DECLARE @comparison VARCHAR(200)
DECLARE @source VARCHAR(200)
DECLARE @supplier VARCHAR(200)
DECLARE @itemname VARCHAR(200)
DECLARE @atc3names VARCHAR(max)

DECLARE c4 CURSOR FOR
SELECT DISTINCT
        [Comparison NAME],     
        [Data Supplier NAME],  
        [Data Source NAME],    
        [Item NAME]
FROM dbo.[Data Validation Attribute Items] WHERE [Comparison NAME] = 'Product'
FOR UPDATE OF [ATC3]

OPEN c4
FETCH NEXT FROM c4 INTO @comparison, @supplier, @source, @itemname

WHILE @@FETCH_STATUS = 0
BEGIN
        -- Combine multile rows in to a column seperated by a ';'
        SET @atc3names  = 'DECLARE @atc3names  AS VARCHAR(200)
        SELECT @atc3names = COALESCE( @atc3names + '';'', '''') + [ATC3 Name]
        FROM
        (
        SELECT DISTINCT [ATC3 Name]
        FROM dbo.[' + @source + ' Fact Data] a
                INNER JOIN dbo.[Product Dim Product-Pack-Country] b
ON a.[Product-Pack-Country KEY] = b.[Product-Pack-Country KEY]
                INNER JOIN dbo.[Product Dim Product-Country] c
ON b.[Product-Country KEY] = c.[Product-Country KEY]
                INNER JOIN dbo.[Product Dim Product-Pack] f 
                        ON b.[Product-Pack KEY] = f.[Product-Pack KEY]
                INNER JOIN dbo.[Product] e ON c.[Product KEY] = e.[Product KEY]
                INNER JOIN dbo.[ATC4] g ON f.[ATC4 KEY] = g.[ATC4 KEY]
                INNER JOIN dbo.[PRODUCT DIM ATC4] h ON h.[ATC4 KEY] = g.[ATC4 KEY]
                INNER JOIN dbo.[ATC3] i ON i.[ATC3 KEY] = h.[ATC3 KEY]
        WHERE e.[Product NAME] = ''' + @itemname + '''
        ) A
       
        UPDATE .dbo.[Data Validation Attribute Items]
        SET [ATC3] = @atc3names
        WHERE [Comparison NAME] = ''Product''
                AND [Data Source NAME] = ''' + @source + '''
                AND [Item NAME] = ''' + @itemname +''''

        EXEC (@atc3names)

        SET @atc3names  = '';

FETCH NEXT FROM c4 INTO @comparison, @supplier, @source, @itemname
END
CLOSE c4
DEALLOCATE c4
GO