Use variable name for column

[jvande]

If I want to update the field Field1 in table, but use the variable for it why won't it work.

set Column_name='Field1'
update table
set Column_name='data'

It looks for a column name of Column_name instead of the column Field1. Is there something wrong with my syntex?

 

[LogicalmanUS]

jvande,

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.

Logicalman

 

[jvande]

it is a stored procedure. Can't you just use something like this?

set Column_name='Field1'
update table
set Column_name='data'

 

[LogicalmanUS]

jvade,

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.

Logicalman

 

[jvande]

ok i have found my answer. Dynamic SQL. Just like you said. Here is an great article on it.

http://www.algonet.se/~sommar/dynamic_sql.html