SQL Server 預存程序實戰：多表關聯新增與事務控制完整範例

業務場景

管理員資料新增涉及 4 張表關聯：Admin (主檔)、Department、Positions、GroupManager (角色)。需在單一交易內完成主檔寫入、取得自增 ID、關聯查詢欄位顯示名、回寫主檔 (示範用)。

預存程序完整碼

CREATE PROCEDURE dbo.InsertAdmin
    @AdminName      VARCHAR(100),
    @AdminPassword  VARCHAR(100),
    @RealName       VARCHAR(50),
    @AdminTypeId    VARCHAR(50),
    @DepartmentId   VARCHAR(50),
    @PositionId     VARCHAR(100)
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRAN;

    -- 1. 寫入主檔
    INSERT INTO Admin (AdminName, AdminPassword, RealName, AdminTypeId, PositionId, DepartmentId)
    VALUES (@AdminName, @AdminPassword, @RealName, @AdminTypeId, @PositionId, @DepartmentId);

    -- 2. 取得剛插入的自增 ID (SCOPE_IDENTITY 避免觸發器干擾)
    DECLARE @NewId INT = SCOPE_IDENTITY();

    -- 3. 多表 JOIN 取得顯示名稱 (示範邏輯，實際可由應用層組裝)
    DECLARE @DeptName VARCHAR(50), @PosName VARCHAR(50), @GroupName VARCHAR(50);
    SELECT 
        @DeptName  = d.DepartName,
        @PosName   = p.PositionName,
        @GroupName = g.GroupName
    FROM Admin a
    JOIN Department d ON a.DepartmentId = d.Id
    JOIN Positions p ON a.PositionId = p.Id
    JOIN GroupManager g ON a.AdminTypeId = g.GroupId
    WHERE a.Id = @NewId;

    -- 4. 示範：若有冗余欄位需回寫 (實際建議由 View 或應用層處理)
    -- UPDATE Admin SET DeptName=@DeptName, PosName=@PosName, GroupName=@GroupName WHERE Id=@NewId;

    COMMIT TRAN;
END
GO

呼叫方式

EXEC dbo.InsertAdmin 'bb', '123', 'sss', '2', '3', '2';
-- 注意：預存程序名後無括號

關鍵知識點解析

常見錯誤避坑

• 忘記 COMMIT → 交易懸掛、鎖定資源、連線池耗盡。
• 使用 @@IDENTITY → 觸發器插入其他表時取得錯誤 ID。
• 動態 SQL 拼接參數 → SQL 注入風險，請用參數化。
• 嘗試在 SP 內建立暫存表並跨批次使用 → 作用域限制，改用全域暫存表 ##temp 或表變數。

擴展想法：動態建表/動態參數

作者嘗試「自定義表 + 動態 SP」但受限於參數上限與動態 SQL 複雜度。現代做法：
• 應用層組裝 JSON/Table-Valued Parameter (TVP) 傳入 SP。
• 或改用 ORM (EF/Dapper) 由應用層控制交易邊界。