本文介绍了更新已添加到表中的列的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

总结:

假设我们有 table1 并且我想在这个表中添加一个名为 column1 的列.然后更新 column1 的值.请注意,我所说的所有内容都发生在 IF 块中.

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.

问题:

如何更新 column1 值而不出现任何错误?

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

错误是:

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

推荐答案

您需要为此使用动态 SQL:

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;

问题是编译时与执行时的问题.在编译时,SQL 解释器会检查所有表和列引用是否有效.当然,它们不是在这种情况下,因为尚未创建相应的列.这就是您必须求助于动态 SQL 的时候.

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.

此外,您可以直接在语句上使用 exec().但是,学习使用 sp_executesql 是个好主意,因为这样可以将参数传入和传出 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.

这篇关于更新已添加到表中的列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

05-27 02:11
查看更多