SQL query when a column may not exist

I needed to run the same T-SQL query against two different databases without knowing if the column AccessMode exists and if the column exist exclude all rows where AccessMode is equal to 1.

The query would fail on the database with the column missing, even though I’ve added an if statement checking for the existence of the column. It seems like the query is interpreted and evaluated before it is actually executed and thus complains about the missing column.

The solution was to declare a variable and conditionally assign it the SQL query to it and then calling EXECUTE() to actually run the query.

DECLARE @SQL As varchar(256)
IF COL_LENGTH('employment.FieldDefinition', 'AccessMode') IS NOT NULL
BEGIN
  SET @SQL = 'SELECT * FROM employment.FieldDefinition WHERE AccessMode != 1'
END ELSE
BEGIN
  SET @SQL = 'SELECT * FROM employment.FieldDefinition'
END
EXECUTE(@SQL)