CODEWITHSUNDEEP
sql-server-2008t-sqlalter-table
Migol
Migol

Reputation: 8448

T-SQL ALTER TABLE not being executed

I'm having a weird problem. Code:

if not exists(select * from sys.columns where Name = N'columnName' and Object_ID = Object_ID(N'[xxx].[yyy]'))
BEGIN
    ALTER TABLE [xxx].[yyy] ADD [columnName] [bit] NOT NULL Default 0
    update [xxx].[yyy] set [columnName] = 1
END

This fragment of SQL file fails with error stating that column [columnName] does not exist on line with update statement. When executed ALTER TABLE line seaprately in SSMS it executes correctly and then procedure works fine. What might be source of this issue?

Upvotes: 2

Views: 3626

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239646

Easiest is to split it into a separate batch. Also, make the ALTER run inside an EXEC, otherwise you may have issues when the column does exist:

if not exists(select * from sys.columns where Name = N'columnName' and
              Object_ID = Object_ID(N'[xxx].[yyy]'))
BEGIN
    exec('ALTER TABLE [xxx].[yyy] ADD [columnName] [bit] NOT NULL Default 0')
END

GO

update [xxx].[yyy] set [columnName] = 1

The issue is that the (simplistic) T-SQL compiler tries to compile an entire batch in one go (batches are separated by GO1s). So it's tries to compile the UPDATE even before it's started executing any logic.

To make it conditional also:

if not exists(select * from sys.columns where Name = N'columnName' and
              Object_ID = Object_ID(N'[xxx].[yyy]'))
BEGIN
    exec('ALTER TABLE [xxx].[yyy] ADD [columnName] [bit] NOT NULL Default 0')
    exec('update [xxx].[yyy] set [columnName] = 1')
END

Where we're effectively using exec to again delay compilation until we know we want it to happen.

Incidentally, in this case I'd probably do it a third way:

if not exists(select * from sys.columns where Name = N'columnName' and
              Object_ID = Object_ID(N'[xxx].[yyy]'))
BEGIN
    exec('ALTER TABLE [xxx].[yyy] ADD [columnName] [bit] NOT NULL constraint DF_XXX Default 1')
    exec('ALTER TABLE xxx.yyy DROP CONSTRAINT DF_XXX')
    exec('ALTER TABLE xxx.yyy ADD CONSTRAINT DF_XXX DEFAULT (0) FOR columnName')
END

Because the first two methods will first update the entire table to have 0 in that column and then update the table a second time to set the value to 1 - whereas this final method should only have to update the existing rows once.

1Batches are split by client tools (such as SSMS) rather than SQL Server. GO is not a SQL Server statement/command, and also, worryingly, is only the default batch separator used by the tools. It's possibly to change from the default to something else, but I'd really recommend against it.

Upvotes: 4

Related Questions