SQL Server 儲存程序入門完全指南：定義、優勢、語法與實戰範例

儲存程序 是 SQL Server 核心程式設計單元，將一組完成特定功能的 T-SQL 陳述式經編譯後儲存在資料庫中。本文從定義、分類、優勢、建立語法到實戰範例，帶你快速上手。

一、什麼是儲存程序？

• 定義：預先編譯、儲存在資料庫中的 T-SQL 批次，透過名稱與參數呼叫執行
• 兩大分類：
  • 系統儲存程序：前綴 sp_，儲存在 master 資料庫，供 DBA 管理執行個體 (如 sp_help, sp_who, sp_configure)
  • 使用者自訂儲存程序：開發者建立，實作業務邏輯，儲存在使用者資料庫

二、四大核心優勢

1. 重複使用：封裝常用邏輯，減少應用程式重複代碼
2. 效能提升：建立時編譯產生執行計劃，後續執行重用計劃，避免重複編譯開銷
3. 降低網路流量：呼叫端只傳「程序名 + 參數」，不傳完整 SQL 批次
4. 安全性增強：
   • 參數化防止 SQL Injection
   • 可獨立授予 EXECUTE 權限，不需給表直接存取權
   • 支援 WITH EXECUTE AS 切換執行內容

三、建立語法完整解析

CREATE PROCEDURE [schema_name.]procedure_name
    @param1 datatype [= default_value] [OUTPUT],
    @param2 datatype [= default_value] [OUTPUT],
    ...
AS
BEGIN
    SET NOCOUNT ON;  -- 抑制「影響 n 筆資料」訊息，減少網路封包
    -- T-SQL 陳述式區塊
    SELECT * FROM Table WHERE Col = @param1;
    -- 可包含流程控制、錯誤處理、交易控制等
END;
GO

關鍵字說明

• @param：參數名稱必須以 @ 開頭
• OUTPUT：標記為輸出參數，呼叫端可接收回傳值
• = default_value：指定預設值，呼叫時可省略該參數
• SET NOCOUNT ON：強烈建議 放在開頭，避免 DONE_IN_PROC 訊息干擾 ORM/驅動程式

四、實戰範例：使用者登入驗證

1. 建立資料表與測試資料

CREATE TABLE UserInfo (
    UserName NVARCHAR(50) PRIMARY KEY,
    UserPass NVARCHAR(100) NOT NULL  -- 實務應存雜湊值
);
INSERT INTO UserInfo VALUES ('admin', 'Admin@123');

2. 建立儲存程序

CREATE PROCEDURE UserLogin
    @name NVARCHAR(50),
    @password NVARCHAR(50)
AS
BEGIN
    SET NOCOUNT ON;
    SELECT UserName, UserPass 
    FROM UserInfo 
    WHERE UserName = @name AND UserPass = @password;
END;
GO

3. 執行與呼叫方式

-- 方式一：位置參數
EXEC UserLogin 'admin', 'Admin@123';

-- 方式二：命名參數 (順序可互換，推薦)
EXEC UserLogin @name = 'admin', @password = 'Admin@123';

-- 方式三：從應用程式呼叫 (C# 範例)
/*
using (var cmd = new SqlCommand("UserLogin", conn) { CommandType = CommandType.StoredProcedure }) {
    cmd.Parameters.AddWithValue("@name", "admin");
    cmd.Parameters.AddWithValue("@password", "Admin@123");
    using (var reader = cmd.ExecuteReader()) {
        if (reader.Read()) { /* 登入成功 *\/ }
    }
}
*/

4. 進階版：回傳狀態碼與輸出參數

CREATE PROCEDURE UserLogin_V2
    @name NVARCHAR(50),
    @password NVARCHAR(50),
    @result INT OUTPUT,           -- 0=失敗, 1=成功
    @userId INT OUTPUT            -- 成功時回傳用戶ID
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @storedHash NVARCHAR(100);
    
    SELECT @storedHash = UserPass, @userId = UserID 
    FROM UserInfo WHERE UserName = @name;
    
    IF @storedHash IS NULL OR @storedHash <> @password
    BEGIN
        SET @result = 0;
        RETURN 0;
    END
    
    SET @result = 1;
    RETURN 1;
END;

呼叫端接收輸出參數：

DECLARE @r INT, @uid INT;
EXEC UserLogin_V2 'admin', 'Admin@123', @r OUTPUT, @uid OUTPUT;
SELECT @r AS Result, @uid AS UserID;

五、管理與維護

• 檢視定義：sp_helptext 'UserLogin' 或 SSMS 物件總管 → 可程式性 → 儲存程序 → 右鍵「修改」
• 修改：ALTER PROCEDURE UserLogin ... (保留權限與相依性)
• 刪除：DROP PROCEDURE UserLogin
• 重新編譯：EXEC sp_recompile 'UserLogin' 或 WITH RECOMPILE 選項 (參數嗅探問題時)
• 加密：CREATE PROCEDURE ... WITH ENCRYPTION (隱藏定義，但可被逆向)

六、最佳實務檢查清單

1. ✅ 所有參數加上型別與預設值
2. ✅ 開頭 SET NOCOUNT ON
3. ✅ 使用 TRY...CATCH 錯誤處理
4. ✅ 寫入操作加上 SET XACT_ABORT ON + 交易控制
5. ✅ 避免 SELECT *，明確列出欄位
6. ✅ 參數化查詢，杜絕動態 SQL 拼接
7. ✅ 輸出參數用 OUTPUT，回傳狀態用 RETURN (僅 INT)
8. ✅ 加上註解說明用途、參數、回傳值、修改記錄