SQL Server 預存程序優勢與結構解析

預存程序四大優勢

基本結構

CREATE PROCEDURE dbo.ProcName
    @InputParam  INT,
    @OutputParam INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;  -- 減少網路封包（DONE_IN_PROC）
    -- 邏輯處理
    SELECT @OutputParam = COUNT(*) FROM dbo.Table WHERE Col = @InputParam;
    RETURN 0;  -- 回傳狀態碼
END;
GO

實務範例：庫存查詢封裝

情境：倉管人員頻繁查詢庫存量與可出貨量。

CREATE PROCEDURE dbo.GetInventoryStatus
    @ProductID INT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT 
        p.ProductName,
        i.QtyOnHand,
        i.QtyAvailable,
        CASE WHEN i.QtyAvailable > 0 THEN '可出貨' ELSE '缺貨' END AS Status
    FROM dbo.Inventory i
    JOIN dbo.Products p ON p.ProductID = i.ProductID
    WHERE i.ProductID = @ProductID;
END;
GO

前端僅需呼叫 EXEC dbo.GetInventoryStatus @ProductID = 123，不需知道表結構與聯結邏輯。

進階技巧

• 動態 SQL：搭配 sp_executesql 參數化，防止注入。
• 錯誤處理：TRY...CATCH 區塊搭配 THROW (SQL 2012+) 或 RAISERROR。
• 交易控制：BEGIN TRAN...COMMIT/ROLLBACK，注意巢狀交易計數 @@TRANCOUNT。
• 暫存表 vs 表變數：大量資料用暫存表（有統計資料、可建索引），小量用表變數（低鎖定、記憶體優先）。

最佳實務清單

1. 命名規範：dbo.動詞_名詞（如 Get_OrderDetail）。
2. 所有參數加型別與預設值，輸出參數明確 OUTPUT。
3. 開頭 SET NOCOUNT ON、SET XACT_ABORT ON（交易自動回滾）。
4. 避免 SELECT *，明確列出欄位。
5. 適度註解：用途、參數、回傳值、修改紀錄。
6. 版本控管：將建立腳本納入資料庫專案。