預存程序 是 SQL Server 封裝業務邏輯、提升效能與安全性的核心物件。本文從概念、分類、語法到實戰範例，帶您全面掌握預存程式的開發精要。

一、什麼是預存程序？

預存程序類似程式語言的「方法/函數」：擁有名稱、接受參數（輸入/輸出）、執行 T-SQL 邏輯、回傳結果集或狀態碼。經編譯後儲存在資料庫中，呼叫時直接執行執行計畫，減少網路傳輸與解析開銷。

核心優勢

優勢	說明
安全性	使用者無需直接表權限，僅授予 EXECUTE 權限即可存取資料。
完整性	將多步驟業務封裝為單一原子操作，確保一致性。
效能	預先編譯、快取執行計畫，減少編譯與網路往返。
維護性	業務邏輯集中資料庫端，修改不需重新部署應用程式。
網路流量	僅傳送呼叫指令與參數，而非大段 T-SQL 字串。

二、預存程序分類

1. 系統預存程序：sp_、xp_ 開頭，儲於 master，所有資料庫可呼叫。
   常用：sp_help、sp_rename、sp_helptext、sp_who、sp_lock、sp_attach_db 等。
2. 擴充預存程序：xp_ 開頭，呼叫外部 DLL（如 xp_cmdshell），安全風險高，建議停用。
3. 使用者自訂預存程序：開發者建立的業務邏輯封裝，主力工具。

三、命名規範

• 建議前綴：usp_ (User Stored Procedure) 或 proc_。
• 採用 PascalCase：usp_GetOrderDetail、proc_UpdateInventory。
• 避免 sp_ 開頭（會優先搜尋 master，造成效能損耗與名稱衝突）。

四、建立語法詳解

CREATE PROC[EDURE] [schema.]ProcedureName
    @Param1 DataType [= DefaultValue] [OUTPUT],
    @Param2 DataType [= DefaultValue] [OUTPUT],
    ...
AS
BEGIN
    SET NOCOUNT ON;  -- 關閉「影響 n 列」訊息，減少網路流量
    -- T-SQL 陳述式
    SELECT ... FROM ... WHERE ... = @Param1;
    -- 回傳值（僅 INT）
    RETURN @ReturnCode;
END;

參數模式

• INPUT（預設）：傳入值。
• OUTPUT：回傳值給呼叫端，需在呼叫時加 OUTPUT 關鍵字。
• 預設值：呼叫時可省略該參數。

五、實戰範例

範例 1：帶輸入/輸出參數的查詢

CREATE PROC usp_GetOrderCountByCity
    @City NVARCHAR(50),
    @OrderCount INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT @OrderCount = COUNT(*)
    FROM SalesInfo
    WHERE Ctiy = @City;
END;

呼叫：

DECLARE @Cnt INT;
EXEC usp_GetOrderCountByCity @City = N'北京', @OrderCount = @Cnt OUTPUT;
PRINT @Cnt;  -- 輸出結果

範例 2：動態分頁查詢（SQL 2012+ OFFSET/FETCH）

CREATE PROC usp_GetPagedOrders
    @PageIndex INT = 1,
    @PageSize  INT = 20,
    @TotalRows INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT @TotalRows = COUNT(*) FROM SalesInfo;
    SELECT OrderID, Ctiy, OrderDate
    FROM SalesInfo
    ORDER BY OrderDate DESC
    OFFSET (@PageIndex - 1) * @PageSize ROWS
    FETCH NEXT @PageSize ROWS ONLY;
END;

範例 3：異常處理與交易控制

CREATE PROC usp_TransferFunds
    @FromAcc INT, @ToAcc INT, @Amount MONEY
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;  -- 發生執行錯誤自動回滾整個交易
    BEGIN TRY
        BEGIN TRAN;
            UPDATE Accounts SET Balance -= @Amount WHERE AccId = @FromAcc;
            UPDATE Accounts SET Balance += @Amount WHERE AccId = @ToAcc;
        COMMIT TRAN;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRAN;
        THROW;  -- 重新拋出錯誤給呼叫端
    END CATCH
END;

六、進階主題

• 加密定義：WITH ENCRYPTION 隱藏程式碼（可被反編譯，非絕對安全）。
• 重新編譯：WITH RECOMPILE 每次執行重新編譯，適用參數嗅探嚴重場景。
• 暫存預存程序：#ProcName（連線級）、##ProcName（全域），連線結束自動清理。
• CLR 預存程序：使用 .NET 語言撰寫複雜運算，透過 CREATE ASSEMBLY 部署。

七、除錯與效能調校

1. 使用 SET STATISTICS IO, TIME ON 分析 IO/CPU。
2. sp_recompile 'ProcName' 強制重新編譯。
3. 查詢執行計畫：SET SHOWPLAN_XML ON 或 SSMS 顯示預估/實際執行計畫。
4. 監控 sys.dm_exec_procedure_stats 找出高耗用程序。

善用預存程序，能將資料存取邏輯標準化、安全化、高效能化，是企業級資料庫開發的基石。