一鍵生成參數化 INSERT 預存程序，消除重複樣板代碼

手寫 `INSERT` 預存程序繁瑣且易錯。系統預存程序 `sp_GenInsert` 可根據資料表結構自動生成標準化 `INSERT` 預存程序代碼，本文深度解析其原理並提供增強版。

原版 `sp_GenInsert` 核心邏輯

1. 輸入：`@TableName` (目標表)、`@ProcedureName` (生成的預存程序名)
2. 查詢 `syscolumns` + `systypes` 獲取欄位名、類型、長度、順序
3. 動態構建：`CREATE PROC ... @param1 type, @param2 type ... AS INSERT INTO ... VALUES (@param1, @param2 ...)`
4. 結果輸出為結果集 (每行一段代碼)，需複製執行才能真正建立預存程序

原版源碼關鍵片段

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

-- 1. 生成 CREATE PROC 行
SELECT 'CREATE PROCEDURE ' + RTRIM(@ProcedureName) AS [text], 0 AS colorder INTO #TempProc
-- 2. 生成參數列表 (@colname type(len),)
UNION SELECT ... FROM syscolumns JOIN systypes ... WHERE id=@TableID AND systypes.name <> 'sysname'
-- 3. AS, INSERT INTO, 欄位列表, VALUES, 參數列表, )
-- 4. 按 colorder 排序輸出
SELECT [text] FROM #TempProc ORDER BY colorder
DROP TABLE #TempProc

使用範例

-- 在 master 建立則全實例可用
USE master; GO
CREATE PROC sp_GenInsert ...; GO

-- 使用
EXEC sp_GenInsert 'Employees', 'usp_Ins_Employees';
-- 複製結果到新視窗執行，即得 usp_Ins_Employees

增強版：直接建立預存程序 + 異常處理 + 權限授予

CREATE PROCEDURE dbo.usp_GenerateInsertProc
  @SchemaName sysname, @TableName sysname, @ProcName sysname = NULL,
  @ExecuteBit bit = 0, @GrantExecuteTo role_name sysname = NULL
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @sql nvarchar(max) = N'', @fullProcName sysname = QUOTENAME(COALESCE(@ProcName, 'usp_Ins_' + @TableName));
  DECLARE @params nvarchar(max) = N'', @cols nvarchar(max) = N'', @vals nvarchar(max) = N'';

  SELECT @params += N', @' + QUOTENAME(c.name) + N' ' + t.name +
         CASE WHEN t.name IN ('varchar','nvarchar','char','nchar','varbinary','binary') THEN N'(' + CAST(c.max_length AS varchar(10)) + N')' ELSE N'' END
       , @cols += N', ' + QUOTENAME(c.name)
       , @vals += N', @' + QUOTENAME(c.name)
  FROM sys.columns c JOIN sys.types t ON c.user_type_id = t.user_type_id
  WHERE c.object_id = OBJECT_ID(QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName))
    AND t.name <> 'sysname' AND c.is_identity = 0;  -- 跳過自增列

  SET @sql = N'CREATE OR ALTER PROCEDURE ' + @fullProcName + STUFF(@params,1,2,N'') + N' AS BEGIN SET NOCOUNT ON; INSERT ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N' (' + STUFF(@cols,1,2,N'') + N') VALUES (' + STUFF(@vals,1,2,N'') + N'); END;';

  IF @ExecuteBit = 1 EXEC sp_executesql @sql;
  IF @GrantExecuteTo IS NOT NULL EXEC(N'GRANT EXECUTE ON ' + @fullProcName + N' TO ' + QUOTENAME(@GrantExecuteTo));

  SELECT @sql AS GeneratedScript;
END

增強亮點

• `CREATE OR ALTER` (SQL 2016+) 避免重複建立報錯
• 自動跳過 `IDENTITY` 欄位
• 支援 `EXECUTE` 直接落地 + `GRANT EXECUTE` 自動授權
• 返回完整腳本供審計/版控

適用場景

代碼生成器後端、標準化 CRUD API 脚手架、遷移腳本批量生成、低代碼平台元數據驅動。