SQL Server 預存程序完整指南：建立、參數、輸出與最佳實務

什麼是預存程序？

預存程序 是儲存在資料庫中、經預先編譯的一組 T-SQL 陳述式集合，類似應用程式中的「方法」或「函式」。它接受輸入參數、執行邏輯、可傳回輸出參數或傳回值，並能將結果集直接回傳給呼叫端。

預存程序的核心優勢

1. 效能：首次執行即產生執行計畫並快取，後續呼叫重複使用，減少編譯開銷。
2. 網路流量：只需傳送 EXEC proc_name params，而非冗長的 T-SQL 批次。
3. 安全性：使用者只需 EXECUTE 權限，無需直接 SELECT/INSERT/UPDATE/DELETE 資料表權限，實現資料存取控制。
4. 完整性：將相關業務邏輯封裝為單一單元，確保資料一致性 (搭配交易)。
5. 維護性：修改邏輯只需更新資料庫端，前端應用程式無須重新部署。
6. 集中化業務規則：企業核心規則集中於資料庫層，避免分散於各應用程式。

預存程序分類

常用系統預存程序速查

EXEC sp_databases;              -- 列出所有資料庫
EXEC sp_tables;                 -- 列出所有資料表
EXEC sp_columns 'student';      -- 列出資料表欄位
EXEC sp_helpindex 'student';    -- 列出索引
EXEC sp_helpconstraint 'student'; -- 列出約束
EXEC sp_helptext 'sp_who';      -- 查看預存程序原始碼
EXEC sp_rename 'student', 'stuInfo'; -- 重新命名資料表
EXEC sp_renamedb 'oldDB', 'newDB';   -- 重新命名資料庫
EXEC sp_helpdb;                 -- 資料庫資訊摘要
EXEC sp_helpdb 'master';        -- 指定資料庫詳細資訊

建立預存程序語法

CREATE PROC[EDURE] [schema.]procedure_name
    @param1 datatype [= default] [OUTPUT],
    @param2 datatype [= default] [OUTPUT],
    ...
AS
BEGIN
    SET NOCOUNT ON; -- 抑制「受影響的資料列數」訊息，提升效能
    -- T-SQL 陳述式
    SELECT ...
    INSERT ...
    UPDATE ...
    DELETE ...
    -- 設定輸出參數
    @output_param = @internal_var
END
GO

四大參數模式實戰範例

1. 無參數 - 直接回傳結果集

CREATE PROCEDURE usp_GetAllOffices
AS
BEGIN
    SET NOCOUNT ON;
    SELECT OfficeID, OfficeName FROM dbo.Office_Info;
END
GO

-- 呼叫
EXEC usp_GetAllOffices;

2. 僅輸入參數 - 依條件篩選

CREATE PROCEDURE usp_GetOfficeById
    @OfficeID INT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT OfficeName FROM dbo.Office_Info WHERE OfficeID = @OfficeID;
END
GO

-- 呼叫 (位置對應)
EXEC usp_GetOfficeById 2;
-- 或具名呼叫 (建議，順序不拘)
EXEC usp_GetOfficeById @OfficeID = 2;

3. 輸出參數 (OUTPUT) - 回傳單一值

CREATE PROCEDURE usp_GetOfficeNameById
    @OfficeID INT,
    @OfficeName NVARCHAR(100) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT @OfficeName = OfficeName FROM dbo.Office_Info WHERE OfficeID = @OfficeID;
END
GO

-- 呼叫 (必須宣告變數接收)
DECLARE @Name NVARCHAR(100);
EXEC usp_GetOfficeNameById @OfficeID = 4, @OfficeName = @Name OUTPUT;
SELECT @Name AS OfficeName;

4. 傳回值 (RETURN) - 回傳狀態碼 (僅 INT)

CREATE PROCEDURE usp_CheckOfficeExists
    @OfficeID INT
AS
BEGIN
    SET NOCOUNT ON;
    IF EXISTS (SELECT 1 FROM dbo.Office_Info WHERE OfficeID = @OfficeID)
        RETURN 1;  -- 存在
    ELSE
        RETURN -1; -- 不存在
END
GO

-- 呼叫 (用變數接收 RETURN 值)
DECLARE @Ret INT;
EXEC @Ret = usp_CheckOfficeExists @OfficeID = 2;
PRINT @Ret; -- 1 或 -1

命名慣例與風格建議

• 前綴：建議 usp_ (User Stored Procedure) 或 proc_，避免 sp_ (系統保留，會先去 master 找，效能稍差)。
• 命名法：PascalCase，如 usp_GetCustomerOrders。
• 縮排：4 空格，關鍵字大寫。
• 註解：標頭註明用途、作者、建立日期、修改紀錄、參數說明、傳回值說明。

進階技巧

動態 SQL 與參數化 (防 SQL Injection)

CREATE PROCEDURE usp_DynamicSearch
    @TableName SYSNAME,          -- 白名單驗證後再用
    @ColName SYSNAME,
    @Value NVARCHAR(100)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @sql NVARCHAR(MAX);
    -- 簡易白名單防注入
    IF @TableName NOT IN (N'Orders', N'Customers') OR @ColName NOT IN (N'CustID', N'OrderID')
        THROW 50001, 'Invalid object name', 1;

    SET @sql = N'SELECT * FROM ' + QUOTENAME(@TableName) + N' WHERE ' + QUOTENAME(@ColName) + N' = @val;';
    EXEC sp_executesql @sql, N'@val NVARCHAR(100)', @val = @Value;
END

交易控制範本

CREATE PROCEDURE usp_TransferFunds
    @FromAcc INT, @ToAcc INT, @Amount MONEY
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRANSACTION;
            UPDATE Accounts SET Balance -= @Amount WHERE AccID = @FromAcc;
            UPDATE Accounts SET Balance += @Amount WHERE AccID = @ToAcc;
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        THROW; -- 重新拋出錯誤給呼叫端
    END CATCH
END

除錯與效能調校

• 查看執行計畫：SSMS 中 Ctrl+M 開啟實際執行計畫，或使用 SET STATISTICS IO, TIME ON。
• 參數嗅探：必要時加入 OPTION (RECOMPILE) 或使用區域變數指派參數值。
• 監控執行統計：

  SELECT OBJECT_NAME(object_id) AS ProcName, execution_count, total_elapsed_time/execution_count AS avg_elapsed_ms
  FROM sys.dm_exec_procedure_stats
  WHERE database_id = DB_ID()
  ORDER BY avg_elapsed_ms DESC;

總結

預存程序是 SQL Server 開發的基石。善用 輸入/輸出參數、傳回值、結果集 三種回傳機制，配合 SET NOCOUNT ON、TRY...CATCH、sp_executesql 參數化動態 SQL，即能建構高效能、安全、可維護的資料存取層。