Invalid column name even after checking if it exists so it should skip the query part

Question

I've got the following query:

    IF EXISTS( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'NotificationTableLogRows' AND COLUMN_NAME = 'DestinationAddress') BEGIN  
      UPDATE NotificationTableLogRows
      SET [HandlerID] = m.ID
      FROM [Unicare].dbo.MobileDevices m
      WHERE HandlerID is null 
      AND m.CallNumber = NotificationTableLogRows.DestinationAddress AND NotificationTableLogRows.HandlerTypeID = 2
    END
print 'end'

But after running this, I still got the error:

Msg 207, Level 16, State 1, Line 6
Invalid column name 'DestinationAddress'.

It should however just print end

What am I doing wrong?

Edit:

Even with:

IF 1 = 0 BEGIN
    UPDATE NotificationTableLogRows
    SET [HandlerID] = m.ID
    FROM [Unicare].dbo.MobileDevices m
    WHERE HandlerID is null 
    AND m.CallNumber = NotificationTableLogRows.DestinationAddress AND NotificationTableLogRows.HandlerTypeID = 2
END

It produces the same error, the problem seems to be in the way sql checks the query pre executing..

Answer 1

This is a parse time error, not a runtime error. You can't use an IF check to determine if a column reference will be ok to use, because the column reference is validated first, long before the IF ever runs (this is why IF 1 = 0 also fails).

Note: The script in the question will appear to work (e.g. it will print end) if you copy the script to your own system where these objects do not exist. Due to deferred name resolution, the parse check will succeed if any of the objects do not exist yet, but will fail if all objects do exist, but any column does not.

As proof, compare:

IF 1 = 0
BEGIN
  SELECT 1 FROM sys.columns AS c
    INNER JOIN sys.objects AS o
    ON c.blat = o.splunge;
END
GO

/* 
  Msg 207, Level 16, State 1
  Invalid column name 'blat'.
  Msg 207, Level 16, State 1
  Invalid column name 'splunge'.
*/

IF 1 = 0
BEGIN
  SELECT 1 FROM dbo.blats AS b
    INNER JOIN dbo.splunges AS s
    ON b.blat = s.splunge;
END
GO

/* 
  "success" 
*/

Now create a table called dbo.blats but without a column named blat:

CREATE TABLE dbo.blats(id int);

The second script still succeeds, because there is still an object that doesn't yet exist, and SQL Server is giving you the benefit of the doubt - at least at parse time - that you'll create it before you execute the script. It will fail when you add the second table, dbo.splunges, but without the referenced column splunge:

CREATE TABLE dbo.splunges(id int);

To get around this, you can use this cumbersome workaround, or just use dynamic SQL (and here I use sys.columns instead of INFORMATION_SCHEMA, well, because):

IF EXISTS 
(
  SELECT 1 
    FROM sys.columns AS c 
    INNER JOIN sys.objects AS o
      ON o.[object_id] = c.[object_id]
    INNER JOIN sys.schemas AS s
      ON o.[schema_id] = s.[schema_id]
    WHERE s.name = N'schema_name'
      AND o.name = N'object_name'
      AND c.name = N'column_name'
)
BEGIN
  EXEC sys.sp_executesql N'UPDATE ... <query that references column_name> ...;';
END

Also here is why I avoid shorthand metadata helpers like OBJECT_NAME(), SCHEMA_NAME(), etc., and instead recommend creating your own helper views.