SQL Server 預存程序四大類型完整教學：無參數、輸入、輸出、返回值

預存程序是預編譯、優化過的 T-SQL 批次，封裝業務邏輯、減少網路往返、提升安全性。本文系統講解四種參數模式。

環境準備

USE TSQLFundamentals2008; -- 範例資料庫
GO

1. 無參數預存程序

CREATE PROC usp_GetAllEmployees
AS
BEGIN
    SELECT * FROM HR.Employees;
END
GO

-- 呼叫
EXEC usp_GetAllEmployees;

適用：固定邏輯、無外部輸入的查詢/維護作業。

2. 僅輸入參數

CREATE PROC usp_GetEmployeeById
    @EmpId INT
AS
BEGIN
    SELECT * FROM HR.Employees WHERE EmpId = @EmpId;
END
GO

-- 呼叫 (命名參數，順序不拘)
EXEC usp_GetEmployeeById @EmpId = 5;

適用：依條件過濾、分頁、動態查詢。

3. 輸入 + 輸出參數

CREATE PROC usp_GetEmployeeWithCount
    @EmpId INT,
    @RowCount INT OUTPUT
AS
BEGIN
    SELECT * FROM HR.Employees WHERE EmpId = @EmpId;
    SET @RowCount = @@ROWCOUNT;  -- 或 SELECT @RowCount = COUNT(*) FROM ...
END
GO

-- 呼叫
DECLARE @Cnt INT;
EXEC usp_GetEmployeeWithCount @EmpId = 5, @RowCount = @Cnt OUTPUT;
SELECT @Cnt AS AffectedRows;

適用：需回傳狀態碼、影響筆數、計算結果等標量值。

4. 返回值 (RETURN)

CREATE PROC usp_CheckEmployeeExists
    @EmpId INT
AS
BEGIN
    IF EXISTS (SELECT 1 FROM HR.Employees WHERE EmpId = @EmpId)
        RETURN 1;
    ELSE
        RETURN 0;
END
GO

-- 呼叫 (必須用變數接收)
DECLARE @Status INT = 0;
EXEC @Status = usp_CheckEmployeeExists @EmpId = 5;
SELECT @Status AS ExistsFlag; -- 1=存在 0=不存在

限制：RETURN 僅支援 INT，且慣例 0=成功，非 0=錯誤碼。複雜資料請用 OUTPUT 參數或結果集。

參數模式選擇指南

最佳實務

• 一律加 SET NOCOUNT ON 減少網路封包。
• 參數命名統一 @PascalCase，加註解說明用途/預設值。
• 輸入參數加預設值 @Param INT = NULL 增加彈性。
• 錯誤處理用 TRY...CATCH，必要時 THROW 保留堆疊。
• 避免 sp_ 前綴 (系統預存程序保留)，防止主資料庫優先解析導致效能損耗。