Update table using nested cursors in SQL Server

Question

Kindly help me to optimize this code, or let me know if I can do this in a better way.

Req: I'm updating Product table which will have product info.

The idea is to fetch some columns from other tables through join. (curTaxonomy is doing that.)
And then update the fetched records to Product table.
But before that I need to check if the columns which I'm updating in the product table are NULL or have dissimilar value (different value than what is coming from join).
If these conditions match then only update the columns.

This I'm using cursors to fetch records and creating dynamic update query which will update the records.

CODE:

CREATE PROCEDURE [dbo].[UpdateProdCatalog]
AS
BEGIN

DECLARE @Tier_1_Tbl NVARCHAR(100) = NULL
DECLARE @Tier_2_Tbl NVARCHAR(100) = NULL    
DECLARE @Flag_Tbl NVARCHAR(100) = NULL

DECLARE @Tier_1 NVARCHAR(100) = NULL
DECLARE @Tier_2 NVARCHAR(100) = NULL
DECLARE @Flag NVARCHAR(100) = NULL  

DECLARE CurTaxonomy CURSOR FOR 
SELECT   CFR.T1, 
         CFR.T2, 
         CFR.Flag
FROM ProductCatalogue MPC
JOIN ControlFlow1 CF ON MPC.Product_Code = CF.Product_Code
JOIN ControlFlow2 CFR ON CF.MajorFamily = CFR.Product_Family
WHERE MPC.Manufacturer_Name = 'DELL';       

OPEN CurTaxonomy
IF @@CURSOR_ROWS > 0    
FETCH NEXT FROM CurTaxonomy INTO @Tier_1,@Tier_2,@Flag  
WHILE @@Fetch_status = 0        
    BEGIN           
    --print @Tier_1 + ' : '+ @Tier_2 + ' : '+ @Flag

        DECLARE CurProd CURSOR FOR 
        SELECT  MPC.Tier_1, 
                MPC.Tier_2, 
                MPC.Flag
        FROM ProductCatalogue MPC   
        WHERE MPC.Manufacturer_Name = 'DELL'

        OPEN CurProd
        IF @@CURSOR_ROWS > 0            
        FETCH NEXT FROM CurProd INTO @Tier_1_Tbl,@Tier_2_Tbl,@Flag_Tbl
        WHILE @@Fetch_status = 0        
            BEGIN                   
                --print @Tier_1_Tbl + ' : '+ @Tier_2_Tbl + ' : '+ @Flag_Tbl

                DECLARE @sqlCommand VARCHAR(8000)       
                DECLARE @Starter VARCHAR(100)

                --Generating dynamic update Query
                SET @Starter = 'SET '                   
                SET @sqlCommand = 'UPDATE ProductCatalogue '                
                BEGIN               
                    IF ( @Tier_1 IS NOT NULL) AND (@Tier_1_Tbl IS NULL or @Tier_1_Tbl <> @Tier_1) 
                        BEGIN   SET @sqlCommand = @sqlCommand + @Starter + 'Tier_1 = ''' + @Tier_1 + ''''  SET @Starter = ', ' END
                    IF ( @Tier_2 IS NOT NULL)  AND (@Tier_2_Tbl IS NULL or @Tier_2_Tbl <> @Tier_2) 
                        BEGIN   SET @sqlCommand = @sqlCommand + @Starter + 'Tier_2 = ''' + @Tier_2 + ''''  SET @Starter = ', ' END
                    IF ( @Flag IS NOT NULL) AND (@Flag_Tbl IS NULL or @Flag_Tbl <> @Flag) 
                        BEGIN   SET @sqlCommand = @sqlCommand + @Starter + 'Flag = ''' + @Flag + ''''  SET @Starter = ', ' END  
                END             

                IF (@Starter = ', ')
                BEGIN           
                    SET @sqlCommand = @sqlCommand + @Starter + ' Last_Modify_Date = ''' + CONVERT(VARCHAR(22),SYSDATETIME(),121) + '''' 
                    SET @sqlCommand = @sqlCommand + ' FROM ProductCatalogue MPC 
                        JOIN ControlFlow1 CF ON MPC.Product_Code = CF.Product_Code
                        JOIN ControlFlow2 CFR ON CF.MajorFamily = CFR.Product_Family 
                        WHERE MPC.Manufacturer_Name = ''' + 'DELL' + ''''               

                    EXEC (@sqlCommand)
                END     

            FETCH NEXT FROM CurProd INTO @Tier_1_Tbl,@Tier_2_Tbl,@Flag_Tbl
        END
        CLOSE CurProd
        DEALLOCATE CurProd  

    FETCH NEXT FROM CurTaxonomy INTO @Tier_1,@Tier_2,@Flag
END
CLOSE CurTaxonomy
DEALLOCATE CurTaxonomy  
END

Here my concern is that the two cursors will run some n x m times and it is also updating the same value to all of the records. I even tried this which works fine.

update ProductCatalogue  
set Tier_1 = CFR.T1, 
    Tier_2 = CFR.T2, 
    Flag=CFR.Flag, 
    Last_Modify_Date = SYSDATETIME()
from ProductCatalogue MPC
join ControlFlow1 CF on MPC.Product_Code = CF.Product_Code
join ControlFlow2 CFR on CF.MajorFamily = CFR.Product_Family;

But I also need to check existing records in product table and fulfill the requirements as mentioned above .If there is any better way to do the same that'll be much appreciated.

You should try to do away with your cursors. You could use the OUTPUT clause of the UPDATE statement to output to a temp table that you could use to update your Product table. Please show us what you want to update in the product table. — Steve Ford, Mar 24 '16 at 11:11
@SteveFord : Hi Steve, I've mentioned that I need to update ,`Tier_1`, `Tier_2`, `Flag` and `Last_Modify_Date` values to `Product` table. That too when these columns are either null or have some different value than what we're getting from joining `Product`, `ControlFlow1` and `ControlFlow2` tables. Refer Select statement for cursor `CurTaxonomy ` for more clarification. — MD SARFARAZ, Mar 24 '16 at 13:45

score 0 · Answer 1 · answered Mar 24 '16 at 13:41

I've come up with this solution so far....But still the cursor execution will loop for N x M times.

CODE:

    CREATE PROCEDURE [dbo].[UpdateProdCatalog]
AS
BEGIN

    DECLARE @Product_Code_Tbl NVARCHAR(100) = NULL
    DECLARE @Tier_1_Tbl NVARCHAR(100) = NULL
    DECLARE @Tier_2_Tbl NVARCHAR(100) = NULL    
    DECLARE @Flag_Tbl NVARCHAR(100) = NULL  

    DECLARE @Product_Code NVARCHAR(100) = NULL
    DECLARE @Tier_1 NVARCHAR(100) = NULL
    DECLARE @Tier_2 NVARCHAR(100) = NULL
    DECLARE @Flag NVARCHAR(100) = NULL          

    DECLARE CurTaxonomy CURSOR FOR 
    SELECT   MPC.Product_Code,
             CFR.T1, 
             CFR.T2, 
             CFR.Flag
    FROM ProductCatalogue MPC
    JOIN ControlFlow1 CF ON MPC.Product_Code = CF.Product_Code
    JOIN ControlFlow2 CFR ON CF.MajorFamily = CFR.Product_Family
    WHERE MPC.Manufacturer_Name = 'DELL';       

    OPEN CurTaxonomy
    IF @@CURSOR_ROWS > 0    
    FETCH NEXT FROM CurTaxonomy INTO @Product_Code, @Tier_1,@Tier_2,@Flag   
    WHILE @@Fetch_status = 0        
        BEGIN                   
            DECLARE CurProd CURSOR FOR 
            SELECT  MPC.Product_Code,
                    MPC.Tier_1, 
                    MPC.Tier_2, 
                    MPC.Flag
                FROM ProductCatalogue MPC
                JOIN ControlFlow1 CF ON MPC.Product_Code = CF.Product_Code  
                JOIN ControlFlow2 CFR ON CF.MajorFamily = CFR.Product_Family
                WHERE MPC.Manufacturer_Name = 'DELL'

            OPEN CurProd
            IF @@CURSOR_ROWS > 0            
            FETCH NEXT FROM CurProd INTO @Product_Code_Tbl,@Tier_1_Tbl,@Tier_2_Tbl,@Flag_Tbl
            WHILE @@Fetch_status = 0        
                BEGIN               
                    DECLARE @sqlCommand VARCHAR(8000)       
                    DECLARE @Starter VARCHAR(100)

                    SET @Starter = 'SET '
                    SET @sqlCommand = 'UPDATE ProductCatalogue '

                    if (@Product_Code IS NOT NULL) AND (@Product_Code_Tbl = @Product_Code)
                    BEGIN           

                        IF ( @Tier_1 IS NOT NULL) AND (@Tier_1_Tbl IS NULL or @Tier_1_Tbl <> @Tier_1) 
                            BEGIN   SET @sqlCommand = @sqlCommand + @Starter + 'Tier_1 = ''' + @Tier_1 + ''''  SET @Starter = ', ' END
                        IF ( @Tier_2 IS NOT NULL) AND (@Tier_2_Tbl IS NULL or @Tier_2_Tbl <> @Tier_2) 
                            BEGIN   SET @sqlCommand = @sqlCommand + @Starter + 'Tier_2 = ''' + @Tier_2 + ''''  SET @Starter = ', ' END
                        IF ( @Flag IS NOT NULL) AND (@Flag_Tbl IS NULL or @Flag_Tbl <> @Flag) 
                            BEGIN   SET @sqlCommand = @sqlCommand + @Starter + 'Flag = ''' + @Flag + ''''  SET @Starter = ', ' END  
                    END                 

                    IF (@Starter = ', ')
                    BEGIN           
                        SET @sqlCommand = @sqlCommand + @Starter + ' Last_Modify_Date = ''' + CONVERT(VARCHAR(22),SYSDATETIME(),121) + '''' 
                        SET @sqlCommand = @sqlCommand +  ' WHERE Product_Code = ''' + @Product_Code + ''' and Manufacturer_Name = ''' + 'DELL' + ''''                   
                        EXEC (@sqlCommand)
                    END     

                FETCH NEXT FROM CurProd INTO @Product_Code_Tbl,@Tier_1_Tbl,@Tier_2_Tbl,@Flag_Tbl
            END
            CLOSE CurProd
            DEALLOCATE CurProd  

        FETCH NEXT FROM CurTaxonomy INTO @Product_Code, @Tier_1,@Tier_2,@Flag   
    END
    CLOSE CurTaxonomy
    DEALLOCATE CurTaxonomy  
END
GO

Update table using nested cursors in SQL Server

1 Answers1