I believe you may be receiving a message similar to 'Column Column_name not found'.
When using variables in SQL statement they must be literal. In stored procedures you can use the simple syntax:
UPDATE myTable SET ID = 2
You may also declare a variable to hold that syntax, and then execute the variable, thus:
DECLARE @sSQL varchar (1000)
SET @sSQL = 'UPDATE myTable SET ID = 2'
EXEC (sSQL)
This will have the same effect as above.
You are now creating a String Variable, and therefore you can manipulate the String, thus:
DECLARE @sSQL varchar (1000)
DECLARE @sColumn varchar(100)
SET @sColumn = 'ID'
SET @sSQL = 'UPDATE myTable SET ' + @sColumn + ' = 2'
EXEC (sSQL)
Again, this will execute as above.
One thing you must remember when concatonating strings and variables (including parameters) together, is that they must all be string variables (I use varchar as the easiest). Therefore if you were to use the following syntax:
DECLARE @iCOUNT int
DECLARE @sSQL varchar (1000)
DECLARE @sColumn varchar(100)
SET @sColumn = 'ID'
SET @iCOUNT = 2
SET @sSQL = 'UPDATE myTable SET ' + @sColumn + ' = ' + @iCOUNT
EXEC (sSQL)
This will fail with an error message stating a type mismatch, something like "could not convert 'UPDATE myTable ...' to type int".
To overcome this you simply CAST the integer variable into a varchar, thus:
DECLARE @iCOUNT int
DECLARE @sSQL varchar (1000)
DECLARE @sColumn varchar(100)
SET @sColumn = 'ID'
SET @iCOUNT = 2
SET @sSQL = 'UPDATE myTable SET ' + @sColumn + ' = ' + CAST(@iCOUNT as varchar (100))
EXEC (sSQL)
Now you get the result you need.
This answer may sound somewhat complicated, but nobody said the solution was easy. I hope it assists you.
When using variables in SPs, you need to prefix them with the @ character. For objects (tables etc) use the # prefix.
My reasons for using the string variable format is that I often write complex SPs that require many parameters and various outputs, so I tend to use the model as per my previous reply.
It also assists in debugging, as you can test the output and variables by using the Print statement with Query Analyzer, without the need to actaully execute any commands against the database.
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.