SQL Server invalid column name

Question

If I try to execute the following code, I get the errors

Msg 207, Level 16, State 1, Line 3 Invalid column name 'Another'. Msg 207, Level 16, State 1, Line 4 Invalid column name 'Another'.

even though the predicate for both IF statements always evaluates to false.

CREATE TABLE #Foo (Bar INT)
GO
IF (1=0)
BEGIN
    SELECT Another FROM #Foo
END
GO
IF (1=0)
BEGIN
    ALTER TABLE #Foo ADD Another INT
    SELECT Another FROM #Foo 
END
GO
DROP TABLE #Foo

This is probably over-simplified for the sake of the example; in reality what I need to do is select the values from a column, but only if the column exists. If it doesn't exist, I don't care about it. In the problem that drove me to ask this question, my predicate was along the lines of EXISTS (SELECT * FROM sys.columns WHERE object_id = @ID AND name = @Name). Is there a way to achieve this without resorting to my arch-enemy Dynamic SQL? I understand that my SQL must always be well-formed (i.e. conform to grammar) - even within a block that's never executed - but I'm flabbergasted that I'm also being forced to make it semantically correct too!

EDIT: Though I'm not sure the code below adds much to the code above, it's a further example of the problem. In this scenario, I only want to set the value of Definitely (which definitely exists as a column) with the value from Maybe (which maybe exists as a column) if Maybe exists.

IF EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('dbo.TableName', 'U') AND name = 'Maybe')
BEGIN
    UPDATE dbo.TableName SET Definitely = Maybe
END

Solution 1

SQL Server doesn't execute line by line. It isn't procedural like .net or Java code. So there is no "non-executed block"

The batch is compiled in one go. At this point, the column doesn't exist but it knows the table will be. Table does not have a column called "Another". Fail.

Exactly as expected.

Now, what is the real problem you are trying to solve?

Some options:

• 2 tables or one table with both columns
• use Stored procedures to decouple scope
• not use temp tables (maybe not needed; it could be your procedural thinking...)
• dynamic SQL (from Mitch's deleted answer)

Edit, after comment;

Why not hide schema changes behind a view, rather than changing all code to work with columns that may/may not be there?

Solution 2

You can use EXEC to handle it. It's not really dynamic SQL if the code never actually changes.

For example:

CREATE TABLE dbo.Test (definitely INT NOT NULL)
INSERT INTO dbo.Test (definitely) VALUES (1), (2), (3)

IF EXISTS (SELECT *
           FROM sys.columns
           WHERE object_id = OBJECT_ID('dbo.Test', 'U') AND
                 name = 'Maybe')
BEGIN
    EXEC('UPDATE dbo.Test SET definitely = maybe')
END

SELECT * FROM dbo.Test

ALTER TABLE dbo.Test ADD maybe INT NOT NULL DEFAULT 999

IF EXISTS (SELECT *
           FROM sys.columns
           WHERE object_id = OBJECT_ID('dbo.Test', 'U') AND
                 name = 'Maybe')
BEGIN
    EXEC('UPDATE dbo.Test SET definitely = maybe')
END

SELECT * FROM dbo.Test

DROP TABLE dbo.Test

Solution 3

You can also try Martin Smith's "Workaround" using a non-existing table to get "deferred name resolution" for columns.

Solution 4

I had the same issue. We are creating a script for all changes for years and this is the first time that we have this issue. I've tried all your answers and didn't find the issue.

In my case it was because of temporary table within the script that I'm using also within a stored procedure, although every sentence has go. I've found that if I'm adding if exists with drop to the temporary table after the script is using the temporary table, it is working correctly.

Best regards, Chen

Solution 5

Derived from the answer by @gbn. What i did to solve the issue was to use 'GO' between the ALTER query and the query that uses the column added by ALTER. This will make the 2 queries to be run as separate batches thereby ensuring your 'Another' column is there before the SELECT query.

View more solutions