sql-server - 测试列是否存在、添加列和更新列

Question

我正在尝试编写 SQL Server 数据库更新脚本。我想测试表中是否存在列，如果不存在，则添加具有默认值的列，最后根据同一个表中不同列的当前值更新该列。我希望这个脚本可以运行多次，第一次更新表，并且在后续运行中应该忽略该脚本。我的脚本目前如下所示:

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'PurchaseOrder' AND COLUMN_NAME = 'IsDownloadable')
BEGIN

ALTER TABLE [dbo].[PurchaseOrder] ADD [IsDownloadable] bit NOT NULL DEFAULT 0

UPDATE [dbo].[PurchaseOrder] SET [IsDownloadable] = 1 WHERE [Ref] IS NOT NULL

END

SQL Server 返回错误“无效的列名 'IsDownloadable'”，即我需要先提交 DDL，然后才能更新列。我尝试了各种排列，但进展缓慢。

Answer 1

除非该列已经存在，而这正是您不需要需要它的时候，否则该脚本不会成功运行。

SQL 脚本必须先进行解析才能执行。如果解析脚本时该列不存在，则解析将失败。您的脚本稍后创建该列并不重要；解析器无法知道这一点。

如果您想访问刚刚添加的列，则需要输入 GO 语句(批分隔符)。但是，一旦执行此操作，您就无法再维护上一批中的任何控制流或变量 - 就像运行两个单独的脚本一样。这使得有条件地同时执行 DDL 和 DML 变得很棘手。

最简单的解决方法(我可能会推荐给您，因为您的 DML 不是很复杂)是使用动态 SQL，解析器在“运行时”之前不会尝试解析:

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'PurchaseOrder' AND COLUMN_NAME = 'IsDownloadable')
BEGIN

    ALTER TABLE [dbo].[PurchaseOrder] ADD 
        [IsDownloadable] bit NOT NULL DEFAULT 0

    EXEC sp_executesql
        N'UPDATE [dbo].[PurchaseOrder] SET [IsDownloadable] = 1 WHERE [Ref] IS NOT NULL'

END