Search code examples
sql-serverstored-procedurescursor

Pass 2 tables as parameters inside cursor


I'm basically trying to execute the same procedure on multiple columns of some tables. I have the procedure, and the cursor statements that access each column, but I cannot pass my tables to the cursor as variables:

Code:

DECLARE @columnName varchar(200),
 @Table1 TABLE(
        [Code] [nvarchar](50) NOT NULL,
        [Name] [nvarchar](255) NOT NULL,
        [Value1] [nvarchar](50) NOT NULL,
        [Value2] [nvarchar](50) NOT NULL,
        [Value3] [nvarchar](50) NOT NULL
    ),
 @Table2 TABLE(
        [Code] [nvarchar](50) NOT NULL,
        [Name] [nvarchar](255) NOT NULL,
        [Value1] [nvarchar](50) NOT NULL
        [Value2] [nvarchar](50) NOT NULL,
        [Value3] [nvarchar](50) NOT NULL
    )
INSERT @Table1 EXEC ProcedureThatAddsData;
INSERT @Table2 EXEC ProcedureThatAddsData;
DECLARE col_cursor CURSOR FOR   

select c.name from sys.tables t inner join sys.columns c on t.object_id = c.object_id where t.name = 'MySpecificTable'

OPEN col_cursor  

FETCH NEXT FROM col_cursor   
INTO @columnName  

WHILE @@FETCH_STATUS = 0  
BEGIN  
    PRINT @columnName
    --BEGIN BODY
    DECLARE @sql nvarchar(4000)


    set @sql = '
    INSERT INTO MySavedDifferencesTable
    select t1.Code, t1.Name, t1.'+ @columnName +' as OldValue, t2.'+ @columnName +' as NewValue
    from '+@Table1+' t1
    join '+@Table2+' t2
        on  t1.Code = t2.Code
        and t1.Name = t2.Name
        WHERE ISNULL(t1.' + @columnName +','') != ISNULL(t2.' + @columnName +','')
    ';
    exec (@sql)



    --end BODY
    FETCH NEXT FROM col_cursor   
    INTO @columnName
END   
CLOSE col_cursor;  
DEALLOCATE col_cursor;  

How can I make this procedure to work. The column selection in cursor is working, so @columnName will iterate through all the columns present in MySpecificTable, but I'm having trouble passing the 2 table variables inside the cursor(for loop?).

Edit:

This is the part that I'm trying to add to my cursor:

@Table1 TABLE(
            [Code] [nvarchar](50) NOT NULL,
            [Name] [nvarchar](255) NOT NULL,
            [Value1] [nvarchar](50) NOT NULL,
            [Value2] [nvarchar](50) NOT NULL,
            [Value3] [nvarchar](50) NOT NULL
        ),
     @Table2 TABLE(
            [Code] [nvarchar](50) NOT NULL,
            [Name] [nvarchar](255) NOT NULL,
            [Value1] [nvarchar](50) NOT NULL
            [Value2] [nvarchar](50) NOT NULL,
            [Value3] [nvarchar](50) NOT NULL
        )
    INSERT @Table1 EXEC ProcedureThatAddsData;
    INSERT @Table2 EXEC ProcedureThatAddsData;

Either declare temporary table variables and insert data into them inside the cursor, or somehow pass outside already full of data temporary tables inside the cursor.


Solution

  • What I ended up doing was to create a temporary table

    -- check if temporary table already exists
        -- if not create it
        IF OBJECT_ID('tempdb..#MyTempTable') IS NULL
        begin
        --table with same columns as snapshot table to insert current data
        --MyTempTable
            CREATE TABLE #MyTempTable(
            [Code] [nvarchar](50) NOT NULL,..
        )
        end
    
        --remove old data if it exists from the temporary MyTempTable table
        delete from #MyTempTable;
        --insert the current data into the temporary MyTempTable table
        INSERT #SSDEStudySpecific EXEC procThatinsertsData;
    

    And now I updated the sql that gets executed inside of my cursor like:

    set @sql = '
        INSERT INTO MySavedDifferencesTable
        select t1.Code, t1.Name, t1.'+ @columnName +' as OldValue, t2.'+ @columnName +' as NewValue
        from '+#MyTempTable1+' t1
        join SomeSystemTable t2
            on  t1.Code = t2.Code
            and t1.Name = t2.Name
            WHERE ISNULL(t1.' + @columnName +','') != ISNULL(t2.' + @columnName +','')
        ';