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

Answer 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?

Answer 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