SQL Server 自動產生 UPDATE 預存程序工具 sp_GenUpdate 完整原始碼 (第三版)

本文提供 sp_GenUpdate 完整可執行原始碼，支援指定主鍵、自動處理所有資料型別長度、參數宣告與 WHERE 條件產生，建立於 master 即可跨資料庫使用。

完整建立腳本

USE master;
GO
IF OBJECT_ID('sp_GenUpdate', 'P') IS NOT NULL DROP PROCEDURE sp_GenUpdate;
GO

CREATE PROCEDURE sp_GenUpdate
    @TableName VARCHAR(130),
    @PrimaryKey VARCHAR(130),
    @ProcedureName VARCHAR(130)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @maxcol INT, @TableID INT;
    SET @TableID = OBJECT_ID(@TableName);

    SELECT @maxcol = MAX(colorder) FROM syscolumns WHERE id = @TableID;

    -- 1. 建立暫存表收集程式碼行
    SELECT 'CREATE PROCEDURE ' + RTRIM(@ProcedureName) AS [text], 0 AS colorder INTO #TempProc
    UNION ALL
    -- 2. 參數宣告
    SELECT '@' + c.name + ' ' + t.name +
        CASE WHEN t.name IN ('binary','char','nchar','nvarchar','varbinary','varchar')
             THEN '(' + RTRIM(CONVERT(CHAR(4), c.length)) + ')'
             ELSE '' END +
        CASE WHEN c.colorder < @maxcol THEN ',' ELSE ' ' END,
        c.colorder
    FROM syscolumns c JOIN systypes t ON c.xtype = t.xtype
    WHERE c.id = @TableID AND t.name <> 'sysname'
    UNION ALL
    -- 3. AS 區塊
    SELECT 'AS', @maxcol + 1
    UNION ALL
    -- 4. UPDATE 陳述式
    SELECT 'UPDATE ' + @TableName, @maxcol + 2
    UNION ALL
    SELECT 'SET', @maxcol + 3
    UNION ALL
    -- 5. SET 子句 (排除主鍵)
    SELECT c.name + ' = @' + c.name +
        CASE WHEN c.colorder < @maxcol THEN ',' ELSE ' ' END,
        c.colorder + @maxcol + 3
    FROM syscolumns c JOIN systypes t ON c.xtype = t.xtype
    WHERE c.id = @TableID AND t.name <> 'sysname' AND c.name <> @PrimaryKey
    UNION ALL
    -- 6. WHERE 條件
    SELECT 'WHERE ' + @PrimaryKey + ' = @' + @PrimaryKey, @maxcol * 2 + 4
    ORDER BY colorder;
END;
GO

使用範例

EXEC sp_GenUpdate 'Employees', 'EmployeeID', 'UPD_Employees';

輸出結果說明

• 自動對應 syscolumns.colorder 保持欄位順序
• 字串型別自動附加長度 NVARCHAR(40) 等
• 主鍵不出現在 SET 子句，僅用於 WHERE
• 結果以 text 欄位逐行回傳，可直接複製執行或導向檔案

部署提示

1. 在 master 建立 → 所有資料庫可直接呼叫
2. 搭配 sp_GenInsert 形成完整 CRUD 產生器
3. 可擴充參數 @ExcludeColumns、@AddAuditFields 等進階功能