在同一条件 IF 语句中更改表添加列并更新新列

2022-01-17 00:00:00 sql-update sql sql-server

我正在尝试在同一个 if 语句中添加列并更新它:

I'm trying to add column and update it in the same if statement:

BEGIN TRAN

IF NOT EXISTS(SELECT 1 FROM sys.columns 
              WHERE Name = N'Code' 
              AND Object_ID = Object_ID(N'TestTable'))
BEGIN
    ALTER TABLE TestTable 
    ADD Code NVARCHAR(10)

    UPDATE TestTable 
    SET Code = Name 
    WHERE 1=1
END

COMMIT

它抛出一个错误:

列名代码"无效

有什么方法可以在一个事务中完成这些操作吗?

Is there any ways how to do these operations in one transaction?

推荐答案

您遇到了解析整个语句的问题，并且由于 Code 列尚不存在而导致 DML 失败.你现在有冲突:

You are running into the issue whereby the entire statement is parsed, and the DML fails because the Code column doesn't exist yet. You now have the conflict:

  • ALTER TABLE 需要 GO(批量执行)
  • 您的多行批处理逻辑需要 BEGIN/END 包装器

您需要找到另一种方法来跨多个语句批次保留添加代码"逻辑的状态，例如使用 #temp 表:

You'll need to find another way to retain the state of 'Add Code' logic across multiple statement batches, e.g. use a #temp table:

CREATE TABLE #tmpFlag(AddCode BIT);

IF NOT EXISTS(SELECT 1 from sys.columns where Name = N'Code' and Object_ID = Object_ID(N'TestTable'))
BEGIN
    INSERT INTO #tmpFlag VALUES(1);
    ALTER TABLE TestTable ADD Code NVARCHAR(10);
END;
GO

IF EXISTS (SELECT * FROM #tmpFlag)
BEGIN
   UPDATE TestTable SET Code = Name;
END;

DROP TABLE #tmpFlag;

这里是SqlFiddle

相关文章