update a column which already added to table

问题:

assume we have table1 and I want to add a column named column1 to this table. and after that update column1's value. note that all I said are happening in IF block. how can I update column1 value without any error?

IF NOT EXISTS (
  SELECT * 
  FROM   sys.columns 
  WHERE  object_id = OBJECT_ID(N'[dbo].[table1]') 
         AND name = 'column1'
)
BEGIN
    ALTER TABLE table1
    ADD column1 BIT NULL

    UPDATE table1 SET table1.column1=0
END

The error is:

Msg 207, Level 16, State 1, Line 245
Invalid column name 'column1'.

回答1:

You need to use dynamic SQL for this:

BEGIN
    DECLARE @sql NVARCHAR(MAX) = 'ALTER TABLE factorOrder ADD column1 BIT NULL';
    EXEC sp_executesql @sql;

    SET @sql = 'UPDATE table1 SET column1 = 0';
    EXEC sp_executesql @sql;
END;

The problem is a compile-time versus execute-time issue. At compile-time, the SQL interpreter checks that all table and column references are valid. Of course, they are not in this case, because the appropriate column hasn't been created. This is when you have to resort to dynamic SQL.

Also, you can use exec() directly on the statement. However, it is a good idea to learn to use sp_executesql because this makes it possible to pass parameters into and out of the SQL statement.