Invalid Column Name Error after ALTER and UPDATE

Question

I am trying to UPDATE a specific column within a table, but I get an error due to the fact that when SQL compiles, the column name IssueTimeUTC does not actually exist. Here is my sample code:

   WHILE  @startissuetime<='23:30'
        BEGIN
        ALTER TABLE Intraday_Forecast_temp
            ADD IssueTimeUTC SMALLDATETIME 

            UPDATE Intraday_Forecast_temp
            SET IssueTimeUTC=CAST(@tempdate AS SMALLDATETIME)
            WHERE DateTimeUTC>CAST(@tempdate AS SMALLDATETIME)

            UPDATE Intraday_Forecast_temp
            SET IssueTimeUTC=Dateadd(d,-1,CAST(@tempdate AS SMALLDATETIME))
            WHERE

DateTimeUTC<=CAST(@tempdate AS SMALLDATETIME) END

I have read multiple similar posts, but I cannot make anything work therefore I am kindly asking you if you could help me.

The error I get is this:

Msg 207, Level 16, State 1, Line 56 Invalid column name 'IssueTimeUTC'.

Update: So basically I was not able to find an exact solution to this specific problem but I just found a way to go ''around'' the problem instead. So this is the updated code I used.

WHILE  @startissuetime<='23:30'
    BEGIN
        ALTER TABLE Intraday_Forecast_temp
        DROP COLUMN IssueTimeUTC 
        --------
        BULK INSERT..... 
        --------
        ALTER TABLE Intraday_Forecast_temp
        ADD IssueTimeUTC SMALLDATETIME 

        UPDATE Intraday_Forecast_temp
        SET IssueTimeUTC=CAST(@tempdate AS SMALLDATETIME)
        WHERE DateTimeUTC>CAST(@tempdate AS SMALLDATETIME)

        UPDATE Intraday_Forecast_temp
        SET IssueTimeUTC=Dateadd(d,-1,CAST(@tempdate AS SMALLDATETIME))
        WHERE DateTimeUTC<=CAST(@tempdate AS SMALLDATETIME)
    END

I know that this is not probably the most elegant solution but I initially defined the Intraday_Forecast_temp table WITH the column IssueTimeUTC , then I drop it and add it again. This way, SQL stop complaining that the column does not exist upon compilation :)

Answer 1

You cannot add the column and use it in the same batch.

So if you execute your code in SSMS just add GO between your commands like this:

    ALTER TABLE Intraday_Forecast_temp
        ADD IssueTimeUTC SMALLDATETIME 

go ----------------------------<<<<<-----------------------------

        UPDATE Intraday_Forecast_temp
        SET IssueTimeUTC=CAST(@tempdate AS SMALLDATETIME)
        WHERE DateTimeUTC>CAST(@tempdate AS SMALLDATETIME)

        UPDATE Intraday_Forecast_temp
        SET IssueTimeUTC=Dateadd(d,-1,CAST(@tempdate AS SMALLDATETIME))
        WHERE DateTimeUTC<=CAST(@tempdate AS SMALLDATETIME)

If it's a part of stored procedure, wrap your update in EXEC:

exec ('UPDATE Intraday_Forecast_temp
        SET IssueTimeUTC=CAST(@tempdate AS SMALLDATETIME)
        WHERE DateTimeUTC>CAST(@tempdate AS SMALLDATETIME)')

UPDATE

In your case you can use wrapping in exec this way:

declare @tempdate date = getdate(); -- or whatever it should be
declare @sql nvarchar(4000)= N'UPDATE Intraday_Forecast_temp
        SET IssueTimeUTC=CAST(@tempdate AS SMALLDATETIME)
        WHERE DateTimeUTC>CAST(@tempdate AS SMALLDATETIME)';

exec sp_executesql @sql, N'@tempdate date', @tempdate = @tempdate;

Answer 2

Just do this.

Execute first the ALTER statement

ALTER TABLE Intraday_Forecast_temp
    ADD IssueTimeUTC SMALLDATETIME

Have it executed successfully then execute the UPDATE statement.

UPDATE Intraday_Forecast_temp
SET IssueTimeUTC=CAST(@tempdate AS SMALLDATETIME)
WHERE DateTimeUTC>CAST(@tempdate AS SMALLDATETIME)

UPDATE Intraday_Forecast_temp
SET IssueTimeUTC=Dateadd(d,-1,CAST(@tempdate AS SMALLDATETIME))
WHERE DateTimeUTC<=CAST(@tempdate AS SMALLDATETIME)