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.
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 +','')
';