SQL Server 2008添加新列时如何避免破坏现有存储过程？

遇到这种因为SELECT *不规范写法导致新增列后存储过程失效的情况，确实挺闹心的，不过有几个靠谱的临时方案能帮你过渡，直到你能批量整改那些存储过程：

方案1：新增列时设置默认值并允许NULL（或强制默认值） #

这是最省心的方法，核心是让SQL Server在插入时自动为新增列填充值，完全不用动存储过程的代码。

操作示例： #

如果新增列允许为空，直接执行：

ALTER TABLE persistant_table ADD NewColumn VARCHAR(100) NULL;

如果业务要求新增列必须非空，就得指定一个符合规则的默认值：

ALTER TABLE persistant_table ADD NewColumn INT NOT NULL DEFAULT 0;

当存储过程执行INSERT INTO persistant_table SELECT * FROM #temp_table_with_data时，SQL Server会自动用NULL（或你指定的默认值）填充新增列，不会因为列数不匹配抛出错误。

注意点： #

• 如果你的业务逻辑不允许新增列有默认值或NULL，这个方案就不适用了。
• 一定要确保默认值符合业务规则，避免产生不符合预期的脏数据。

方案2：创建INSTEAD OF INSERT触发器拦截插入操作 #

如果方案1不符合业务要求（比如新增列需要根据其他字段动态计算，或者不能用固定默认值），可以用触发器来重新映射插入的列。

操作示例： #

创建一个触发器，接管原来的插入逻辑，手动指定要插入的列，同时为新增列设置合适的值：

CREATE TRIGGER trg_persistant_table_InsteadOfInsert
ON persistant_table
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;
    -- 明确列出目标表的所有列（包括新增列）
    INSERT INTO persistant_table (Col1, Col2, Col3, NewColumn)
    SELECT 
        Col1, Col2, Col3, 
        GETDATE() -- 这里可以设置固定值、动态计算值，甚至关联其他表数据
    FROM inserted;
END;

这样当存储过程执行插入时，触发器会自动处理列的映射，把临时表的原有列对应到目标表的对应字段，同时为新增列填充你需要的值，完全不需要修改存储过程的代码。

注意点： #

• 触发器会带来一点点性能开销，不过对于大多数业务场景来说可以忽略。
• 如果后续还要新增列，需要同步更新触发器的逻辑，所以这只是临时过渡方案。

长期优化建议 #

虽然上面的方案能解决当前问题，但强烈建议你尽快整改那些使用SELECT *的存储过程，把模糊的SELECT *改成明确列出所有列名。比如把：

INSERT INTO persistant_table SELECT * FROM #temp_table_with_data

改成：

INSERT INTO persistant_table (Col1, Col2, Col3, NewColumn)
SELECT Col1, Col2, Col3, 'DefaultValue' FROM #temp_table_with_data

这样以后再修改表结构时，就不会因为列顺序或数量变化导致存储过程失效，代码也会更具可读性和维护性。

内容的提问来源于stack exchange，提问作者jmich738