user7525429 user7525429 - 4 years ago 77
SQL Question

If Exist statement is running when it should not -- MS SQL Server 2014 error

In the below statement

Field1
does not exist.
IF EXIST
will return false and the select statement on line 3 should not run.

IF EXISTS (SELECT *
FROM Database1.sys.columns
WHERE NAME = 'Field1'
AND OBJECT_ID = OBJECT_ID('Database1.dbo.Table1'))
SELECT D.Field1
FROM Database1.dbo.Table1 D


However, when I run this, I get this error:


Msg 207, Level 16, State 1, Line 3

Invalid column name 'Field1'.


Is this a bug or am I doing something wrong? I need to query the table if
Field1
exists.

Please note that database1 exists and field1 does not exist.

Answer Source

You'll need to use dynamic SQL:

IF EXISTS (SELECT * FROM  Database1.sys.columns WHERE NAME = 'Field1' 
AND OBJECT_ID = OBJECT_ID('Database1.dbo.Table1')) 
    EXEC('SELECT D.Field1 FROM Database1.dbo.Table1 D');
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download