问题描述
总结:
假设我们有 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.
这篇关于更新已添加到表中的列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!