给出以下sql：
IF EXISTS (SELECT * FROM sys.columns WHERE name = 'NewFieldName' AND object_id = OBJECT_ID('dbo.MyTableName'))
RETURN
-- Add NewFieldName column to part of the Summer 2012 release cycle.
ALTER TABLE dbo.[MyTableName] ADD
[NewFieldName] SmallINT NOT NULL
CONSTRAINT DF_MyTableName_NewFieldName DEFAULT (2)
UPDATE [MyTableName] SET NewFieldName = 1 WHERE [Name] = 'FindMe' --Update one specific value
产生以下错误消息：
Msg 207,Level 16,State 1,Line 10 Invalid column name
‘NewFieldName’.
我确定我错过了一些基本的东西,但是尝试在“更改”之后放置“GO”,每次都会执行UPDATE,而我不想这样做.
如何构造这个语句,以便它会检查列是否存在,如果它不添加,然后设置在UPDATE语句中所述的值？
解决方法
在添加新列之后,需要引用要编译的新列的语句.执行此操作的一种方法是以EXEC作为子批次运行.
IF NOT EXISTS (SELECT *
FROM sys.columns
WHERE name = 'NewFieldName'
AND object_id = OBJECT_ID('dbo.MyTableName'))
BEGIN
-- Add NewFieldName column to part of the Summer 2012 release cycle.
ALTER TABLE dbo.[MyTableName]
ADD [NewFieldName] SMALLINT NOT NULL
CONSTRAINT DF_MyTableName_NewFieldName DEFAULT (2)
EXEC(' UPDATE [MyTableName] SET NewFieldName = 1 WHERE [Name] = ''FindMe''')
END
原来它最适合你的原因可能是因为当批处理被编译时表本身不存在,这意味着引用该表的所有语句都会被延期编译.
