T-Sql appears to be evaluating "If" statement even when the condition is not true

Question

I have a T-Sql script where part of this script checks to see if a certain column exists in the a table. If so, I want it to execute a routine... if not, I want it to bypass this routine. My code looks like this:

IF COL_LENGTH('Database_Name.dbo.Table_Name', 'Column_Name1') IS NOT NULL
    BEGIN
        UPDATE Table_Name
            SET Column_Name2 = (SELECT Column_Name3 FROM Table_Name2
                WHERE Column_Name4 = 'Some Value')
            WHERE Column_Name5 IS NULL;

        UPDATE Table_Name
            SET Column_Name6 = Column_Name1
            WHERE Column_Name6 IS NULL;
    END

My problem is that even when COL_LENGTH of Column_Name1 is null (meaning it does not exist) I am still getting an error telling me "Invalid column name 'Column_Name1'" from the 2nd UPDATE statement in the IF statement. For some reason this IF condition is still being evaluated even when the condition is FALSE and I don't know why.

Answer 1

SQL Server parses the statement and validates it, ignoring any if conditionals. This is why the following also fails:

IF 1 = 1
BEGIN
  CREATE TABLE #foo(id INT);
END
ELSE
BEGIN
  CREATE TABLE #foo(id INT);
END

Whether you hit Execute or just Parse, this results in:

Msg 2714, Level 16, State 1
There is already an object named '#foo' in the database.

SQL Server doesn't know or care which branch of a conditional will be entered; it validates all of the statements in a batch anyway. You can do things like (due to deferred name resolution):

IF <something>
BEGIN
  SELECT foo FROM dbo.Table_That_Does_Not_Exist;
END

But you can't do:

IF <something>
BEGIN
  SELECT column_that_does_not_exist FROM dbo.Table_That_Does;
END

The workaround, typically, is to use dynamic SQL:

IF <something>
BEGIN
  DECLARE @sql NVARCHAR(MAX);
  SET @sql = N'SELECT column_that_does_not_exist FROM dbo.Table_That_Does;';
  EXEC sp_executesql @sql;
END

Answer 2

Make your statement a string. And if column exists, execute it

IF COL_LENGTH('Database_Name.dbo.Table_Name', 'Column_Name1') IS NOT NULL
BEGIN
    DECLARE @sql VARCHAR(MAX)
    SET @sql = 'UPDATE Table_Name
            SET Column_Name2 = (SELECT Column_Name3 FROM Table_Name2
                WHERE Column_Name4 = ''Some Value'')
            WHERE Column_Name5 IS NULL;

        UPDATE Table_Name
            SET Column_Name6 = Column_Name1
            WHERE Column_Name6 IS NULL;'
     EXEC(@sql)
END