Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Freddy
Starting Member
3 Posts |
 Posted - 2011-10-05 : 13:33:34
|
| Hi, i have a table in which the column names are created dinamically, and then i want to update each one of the columns and i've tried something like this:"IF EXISTS (select * from sys.columns where Name = N'ColumnName1' and Object_ID = Object_ID(N'TableName'))BEGIN UPDATE TableName SET ColumnName1 = 1END"and the output is : "Msg 207, Level 16, State 1, Line 7Invalid column name 'ColumnName1'."I know that some of the columns doesn't exist, but is there a way to override the error message or another way to do this?ThanksFreddy |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2011-10-05 : 13:46:10
|
| [code]IF EXISTS (select * from sys.columns where Name = N'ColumnName1' and Object_ID = Object_ID(N'TableName')) BEGIN exec ('UPDATE TableName SET ColumnName1 = 1') END[/code]CODO ERGO SUM |
 |
|
|
Freddy
Starting Member
3 Posts |
Posted - 2011-10-05 : 14:14:25
|
| Thank u Michael, it really worked, but can u please explain why? |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-10-05 : 14:54:58
|
| The error is a parse-time error, long before the IF gets executed. When SQL parses the batch it checks all statements for validity, and that includes columns actually existing. The only way to get around that is to run the update in a different scope, which is what the dynamic SQL does.--Gail ShawSQL Server MVP |
|
|
|
Freddy
Starting Member
3 Posts |
Posted - 2011-10-05 : 15:05:29
|
| Thanks a lot. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
If Exists problem