您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 自動產生 INSERT/UPDATE 預存程序工具：sp_GenInsert 與 sp_GenUpdate 完整碼與使用指南

編輯：關於SqlServer

開發資料庫應用時，為每張表手寫 CRUD 預存程序既繁瑣又易出錯。本文提供兩個系統預存程序 sp_GenInsert、sp_GenUpdate，可依資料表中繼資料自動產生標準化的 Insert/Update 程式碼，大幅提升開發效率。

一、 sp_GenInsert：自動產生 INSERT 預存程序

語法

EXEC sp_GenInsert '資料表名', '產生的預存程序名';
-- 範例
EXEC sp_GenInsert 'Employees', 'INS_Employees';

產出範例 (Northwind.Employees)

CREATE PROCEDURE INS_Employees
    @LastName NVARCHAR(20),
    @FirstName NVARCHAR(10),
    @Title NVARCHAR(30),
    @TitleOfCourtesy NVARCHAR(25),
    @BirthDate DATETIME,
    @HireDate DATETIME,
    @Address NVARCHAR(60),
    @City NVARCHAR(15),
    @Region NVARCHAR(15),
    @PostalCode NVARCHAR(10),
    @Country NVARCHAR(15),
    @HomePhone NVARCHAR(24),
    @Extension NVARCHAR(4),
    @Photo IMAGE,
    @Notes NTEXT,
    @ReportsTo INT,
    @PhotoPath NVARCHAR(255)
AS
INSERT INTO Employees (LastName, FirstName, Title, TitleOfCourtesy,
    BirthDate, HireDate, Address, City, Region, PostalCode, Country,
    HomePhone, Extension, Photo, Notes, ReportsTo, PhotoPath)
VALUES (@LastName, @FirstName, @Title, @TitleOfCourtesy,
    @BirthDate, @HireDate, @Address, @City, @Region, @PostalCode, @Country,
    @HomePhone, @Extension, @Photo, @Notes, @ReportsTo, @PhotoPath);
GO

注意事項

若表有 IDENTITY 欄位，產生的程式碼會包含 SET IDENTITY_INSERT ON，需手動移除或視需求保留。
建立於 master 資料庫且加上 sp_ 前綴，即可跨資料庫呼叫。

二、 sp_GenUpdate：自動產生 UPDATE 預存程序

語法

EXEC sp_GenUpdate '資料表名', '主鍵欄位', '產生的預存程序名';
-- 範例
EXEC sp_GenUpdate 'Employees', 'EmployeeID', 'UPD_Employees';

產出範例

CREATE PROCEDURE UPD_Employees
    @EmployeeID INT,
    @LastName NVARCHAR(20),
    @FirstName NVARCHAR(10),
    @Title NVARCHAR(30),
    @TitleOfCourtesy NVARCHAR(25),
    @BirthDate DATETIME,
    @HireDate DATETIME,
    @Address NVARCHAR(60),
    @City NVARCHAR(15),
    @Region NVARCHAR(15),
    @PostalCode NVARCHAR(10),
    @Country NVARCHAR(15),
    @HomePhone NVARCHAR(24),
    @Extension NVARCHAR(4),
    @Photo IMAGE,
    @Notes NTEXT,
    @ReportsTo INT,
    @PhotoPath NVARCHAR(255)
AS
UPDATE Employees SET
    LastName = @LastName,
    FirstName = @FirstName,
    Title = @Title,
    TitleOfCourtesy = @TitleOfCourtesy,
    BirthDate = @BirthDate,
    HireDate = @HireDate,
    Address = @Address,
    City = @City,
    Region = @Region,
    PostalCode = @PostalCode,
    Country = @Country,
    HomePhone = @HomePhone,
    Extension = @Extension,
    Photo = @Photo,
    Notes = @Notes,
    ReportsTo = @ReportsTo,
    PhotoPath = @PhotoPath
WHERE EmployeeID = @EmployeeID;
GO

三、核心原始碼 (建立於 master)

sp_GenInsert

USE master; GO
CREATE PROCEDURE sp_GenInsert
    @TableName VARCHAR(130),
    @ProcedureName VARCHAR(130)
AS
SET NOCOUNT ON;
DECLARE @maxcol INT, @TableID INT;
SET @TableID = OBJECT_ID(@TableName);
SELECT @maxcol = MAX(colorder) FROM syscolumns WHERE id = @TableID;

SELECT 'CREATE PROCEDURE ' + RTRIM(@ProcedureName) AS [text], 0 AS colorder INTO #TempProc
UNION ALL
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
SELECT 'AS', @maxcol + 1
UNION ALL
SELECT 'INSERT INTO ' + @TableName, @maxcol + 2
UNION ALL
SELECT '(', @maxcol + 3
UNION ALL
SELECT 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'
UNION ALL
SELECT ')', @maxcol * 2 + 4
UNION ALL
SELECT 'VALUES (', @maxcol * 2 + 5
UNION ALL
SELECT '@' + c.name + CASE WHEN c.colorder < @maxcol THEN ',' ELSE ' ' END,
    c.colorder + @maxcol * 2 + 5
FROM syscolumns c JOIN systypes t ON c.xtype = t.xtype
WHERE c.id = @TableID AND t.name <> 'sysname'
UNION ALL
SELECT ')', @maxcol * 3 + 6
ORDER BY colorder;
GO

sp_GenUpdate (關鍵差異：WHERE 條件鎖定 PK)

CREATE PROCEDURE sp_GenUpdate
    @TableName VARCHAR(130),
    @PrimaryKey VARCHAR(130),
    @ProcedureName VARCHAR(130)
AS
SET NOCOUNT ON;
DECLARE @maxcol INT, @TableID INT;
SET @TableID = OBJECT_ID(@TableName);
SELECT @maxcol = MAX(colorder) FROM syscolumns WHERE id = @TableID;

-- 參數宣告、AS、UPDATE、SET 同 sp_GenInsert，最後加上：
UNION ALL
SELECT @PrimaryKey + ' = @' + @PrimaryKey, @maxcol * 2 + 5  -- WHERE 條件
ORDER BY colorder;
GO