Vairable based column names

[SQLScholar]

Hey all,

I hope you can help me.

I am having to find out if a table has changed since last run. Since i have no ability to change structure or inserts - the only way i can think to do it is to take a copy and do a compare against yesterdays copy.

So i can get a list of all the PK`s that have changed. So - the problem is now i need to know whats changed.

So i have a loop to return the ID number of the record. Then inside that i have a loop of all the column names (taken from systables). Inside that i have this SQL query task.

Insert into [HR Changes]

select
hr.id,'TSORT' as [Column] , HR.tsort
from 
HR
inner join 
[HR last] on
HR.id = [HR last].id

where HR.id = ?

and case when  hr.tsort = [HR last].tsort then 'TRUE' else 'FALSE' end = 'TRUE'

Passing ID works fine.
Now where ever it has tsort i want to replace it with a parameter. So i do the same thing (i havent even referenced the param in the SQL), but i get the error:


[Execute SQL Task] Error: Executing the query "Insert into [HR Changes]

[Execute SQL Task] Error: Executing the query "Insert into [HR Changes]

select
hr.id,'TSORT' as ..." failed with the following error: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

I have tried the parameter name being 1 and 0.

So i tried using the @parameter type. If i set the parameter name to @id and reference it i get.

select
hr.id,'TSORT' as ..." failed with the following error: "Must declare the scalar variable "@id".". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

What am i doing wrong?

Dan

----------------------------------------
Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Dr. Seuss

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------