SQL Question
Microsoft SQL Invalid Column Name Inside of Conditional Statement
I am using a Database Update Script which works based on versions. It has a section for each version following this basic format:
if (SELECT max(version) FROM DatabaseVersion) < x
BEGIN
/*CODE*/
INSERT INTO DatabaseVersion ....... (Current Version Number, Description of Change)
END
One of the old updates was to change column names. Today, when I try to update the database again with this script, I am getting "Invalid column name" errors. I have debugged, and found that the error is happening on a line that cannot possibly be executed (since the Database Version is already higher than the "x" value in that conditional statement). I ran a SELECT query to test if that SELECT query would execute, and it does not. This indicates to me that the code is being "checked over" by the SQL server before being executed, which means that the FALSE on the conditional statement does not prevent the server from reviewing that code, and subsequently giving me that error.
How can I prevent, or bypass this, without changing any of the update code (which follows the format above)?
Recommended for you: Get network issues from WhatsUp Gold. Not end users.
Answer Source
Just wrap your code that does not compile in EXEC ():
if (SELECT max(version) FROM DatabaseVersion) < x
exec('INSERT INTO DatabaseVersion ....... (Current Version Number, Description of Change)')
Here is a picture where exec has executed more than 128 characters of code:
And here is the link to the documentation: EXECUTE-Transact-SQL
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download