CODEWITHSUNDEEP
sqlsql-server
TLamb
TLamb

Reputation: 147

Why is this SQL syntax throwing an error?

I keep getting an error when executing this statement in a longer script:

IF EXISTS (SELECT 1 FROM sys.columns
           WHERE name = N'uncertainty_requested' 
             AND object_id = object_id(N'workorder'))
BEGIN
    PRINT 'workorder.uncertainty_requested exists';
END
ELSE
BEGIN
    ALTER TABLE workorder
        ADD uncertainty_requested CHAR(1);

    UPDATE workorder
    SET uncertainty_requested = 'F';

    PRINT 'workorder.uncertainty_requested added.'
END 
GO

The error message is:

Msg 207, Level 16, State 1, Line 10
Invalid column name 'uncertainty_requested'.

Using SQL Server 2012.

Upvotes: 1

Views: 79

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

The code is compiled before it is executed, and the missing column is a compile-time error. You can handle this using dynamic SQL:

ALTER TABLE workorder
    ADD uncertainty_requested CHAR(1);
exec sp_executesql N'UPDATE workorder SET uncertainty_requested = ''F''';
PRINT 'workorder.uncertainty_requested added.'

Upvotes: 7

Related Questions