Search code examples
sqlsql-server-2005ddl

SQL Updating column after adding it giving "Invalid column name" error

I have the following SQL in SQL Server 2005 but I get an error stating "Invalid column name 'ExpIsLocalTime' (ln 7) when I run it:

IF NOT EXISTS(SELECT * FROM sys.columns WHERE Name = N'ExpIsLocalTime' AND Object_ID = Object_ID(N'[dbo].[tbl_SessionsAvailable]'))
BEGIN
    ALTER TABLE dbo.tbl_SessionsAvailable ADD
    ExpIsLocalTime bit NOT NULL CONSTRAINT DF_tbl_SessionsAvailable_ExpIsLocalTime DEFAULT (0)

    UPDATE  dbo.tbl_SessionsAvailable
    SET     ExpIsLocalTime = 1
END
GO

This will be in a script file that may be run more than once so I'm trying to make sure the UPDATE only runs once. Is there something about BEGIN/END that delays the execution of the DDL statement?

Solution

  • Your SQL query to do the UPDATE refers to a column that has not yet been created. At compile time, SQL Server detects that the column does not exist, so it gives you the error "Invalid column name 'ExpIsLocalTime'".

    In order to include the UPDATE in this query, you will need to encapsulate it in a dynamic SQL query. In other words, something like this:

    IF NOT EXISTS(SELECT * FROM sys.columns WHERE Name = N'ExpIsLocalTime' AND Object_ID = Object_ID(N'[dbo].[tbl_SessionsAvailable]'))
BEGIN
    ALTER TABLE dbo.tbl_SessionsAvailable ADD
    ExpIsLocalTime bit NOT NULL CONSTRAINT DF_tbl_SessionsAvailable_ExpIsLocalTime DEFAULT (0)

    DECLARE @SQL NVARCHAR(1000)
    SELECT @SQL = N'UPDATE dbo.tbl_SessionsAvailable SET ExpIsLocalTime = 1'
    EXEC sp_executesql @SQL
END
GO

    We have the same issue in our SQL scripts that maintain tables. After a table is created, if we add a column to it later, we have to use dynamic SQL to avoid these compilation errors.