Why does a T-SQL block give an error even if it sh

问题:

I was writing a (seemingly) straight-forward SQL snippet that drops a column after it makes sure the column exists.
The problem: if the column does NOT exist, the code inside the IF clause complains that it can't find the column! Well, doh, that's why it's inside the IF clause!
So my question is, why does a piece of code that shouldn't be executed give errors?

Here's the snippet:

IF exists (select * from syscolumns
    WHERE id=object_id('Table_MD') and name='timeout')
BEGIN
    ALTER TABLE [dbo].[Table_MD]
        DROP COLUMN timeout
END
GO

...and here's the error:

Error executing SQL script [...]. Invalid column name 'timeout'

I'm using Microsoft SQL Server 2005 Express Edition.

回答1:

IF exists (select * from syscolumns
    WHERE id=object_id('Table_MD') and name='timeout')
BEGIN
    DECLARE @SQL nvarchar(1000)
    SET @SQL = N'ALTER TABLE [dbo].[Table_MD] DROP COLUMN timeout'
    EXEC sp_executesql @SQL
END
GO

Reason: When Sql server compiles the code, they check it for used objects ( if they exists ). This check procedure ignores any "IF", "WHILE", etc... constructs and simply check all used objects in code.

回答2:

It may never be executed, but it's parsed for validity by Sql Server. The only way to "get around" this is to construct a block of dynamic sql and then selectively execute it

回答3:

Here's how I got it to work:

Inside the IF clause, I changed the ALTER ... DROP ... command with exec ('ALTER ... DROP ...')

It seems the SQL server does a validity check on the code when parsing it, and sees that a non-existing column gets referenced somewhere (even if that piece of code will never be executed).
Using the exec(ute) command wraps the problematic code in a string, the parser doesn't complain, and the code only gets executed when necessary. Here's the modified snippet:

IF exists (select * from syscolumns
    WHERE id=object_id('Table_MD') and name='timeout')
BEGIN
    exec ('ALTER TABLE [dbo].[Table_MD] DROP COLUMN timeout')
END
GO

回答4:

By the way, there is a similar issue in Oracle, and a similar workaround using the "execute immediate" clause.