October 25, 2017 at 3:54 am
Hi All,
I am writing a generic script for all our databases to return the value from a field called AppVersion in a table called System. Some of our databases do not contain this field so I wrapped the statement in a IF EXISTS to prevent an error
IF EXISTS (select * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'System' AND COLUMN_NAME = 'AppVersion')
BEGIN
SELECT AppVersion FROM dbo.System
END
When executed on a database that does not have the AppVersion column SQL returns the following error.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'AppVersion'.
I am confused as to why SQL is running the statement if the condition of IF EXISTS is not met
Any guidance would be much appreciated
Regards
David
October 25, 2017 at 5:19 am
TerrenceTheCat - Wednesday, October 25, 2017 3:54 AMHi All,I am writing a generic script for all our databases to return the value from a field called AppVersion in a table called System. Some of our databases do not contain this field so I wrapped the statement in a IF EXISTS to prevent an error
IF EXISTS (select * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'System' AND COLUMN_NAME = 'AppVersion')
BEGIN
SELECT AppVersion FROM dbo.System
ENDWhen executed on a database that does not have the AppVersion column SQL returns the following error.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'AppVersion'.
I am confused as to why SQL is running the statement if the condition of IF EXISTS is not metAny guidance would be much appreciated
Regards
David
Try executing the query as dynamic SQL.IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'System' AND COLUMN_NAME = 'AppVersion')
BEGIN
EXEC sys.sp_executesql N'SELECT AppVersion FROM dbo.System'
END;
October 25, 2017 at 5:37 am
David
Des's solution using dynamic SQL is right. The reason your solution fails is that you get a parse-time error. The query parser doesn't take account of control-flow logic (IF statements), checking instead that all objects and columns specified in the query exist. That's why you need to fool it with dynamic SQL.
The necessity for logic like this often points to a poorly designed database: a well-designed database will not change over time (except when a new version is released) and so checking for existence of objects and columns should not be necessary. A lot of the time, we're forced to query a database over whose structure we have not control - I understand that!
John
October 25, 2017 at 6:18 am
Des, John
Thank you so much - the solution works perfectly. I have learnt something new about the control logic as well - thank you
Regards
David
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply