SQL Server 儲存程序全攻略：從入門到進階實戰

什麼是儲存程序？

儲存程序 是一組為完成特定功能而編譯後永久儲存在資料庫中的 T-SQL 陳述式集合。使用者透過呼叫程序名稱並傳遞參數即可執行。它類似於 Java 的方法，支援輸入/輸出參數、回傳結果集與回傳值，並包含邏輯控制（IF/WHILE/TRY-CATCH）與資料操作（DML/DDL）語句。

四大核心優勢

系統儲存程序速查表

系統程序儲存於 master 資料庫，以 sp_ 開頭，任何資料庫皆可呼叫。常用範例：

EXEC sp_databases;          -- 列出所有資料庫
EXEC sp_tables;             -- 列出所有資料表
EXEC sp_columns 'student';  -- 檢視資料表欄位
EXEC sp_helpindex 'student';-- 檢視索引
EXEC sp_helptext 'sp_who';  -- 檢視程序定義
EXEC sp_rename 'stu', 'stud';         -- 重命名資料表
EXEC sp_rename 'stud.name', 'sName', 'COLUMN'; -- 重命名欄位

使用者自訂儲存程序完整語法

CREATE PROC | PROCEDURE proc_name
    [ @param_name data_type [ = default ] [ OUTPUT ] , ... ]
AS
    sql_statements

十大實戰範例分類

1. 無參數程序

IF OBJECT_ID('proc_get_student', 'P') IS NOT NULL DROP PROC proc_get_student;
GO
CREATE PROC proc_get_student AS SELECT * FROM student;
GO
EXEC proc_get_student;

2. 修改程序

ALTER PROC proc_get_student AS SELECT * FROM student WHERE age > 18;

3. 輸入參數

CREATE PROC proc_find_stu @startId INT, @endId INT
AS SELECT * FROM student WHERE id BETWEEN @startId AND @endId;
GO
EXEC proc_find_stu 2, 4;

4. 含預設值與萬用字元參數

CREATE PROC proc_findStudentByName
    @name VARCHAR(20) = '%j%', @nextName VARCHAR(20) = '%'
AS SELECT * FROM student WHERE name LIKE @name AND name LIKE @nextName;
GO
EXEC proc_findStudentByName;                -- 使用預設值
EXEC proc_findStudentByName '%o%', 't%';    -- 傳入自訂萬用字元

5. 輸出參數（OUTPUT）

CREATE PROC proc_getStudentRecord
    @id INT,
    @name VARCHAR(20) OUTPUT,
    @age VARCHAR(20) OUTPUT
AS SELECT @name = name, @age = age FROM student WHERE id = @id;
GO

DECLARE @id INT = 7, @name VARCHAR(20), @age VARCHAR(20);
EXEC proc_getStudentRecord @id, @name OUTPUT, @age OUTPUT;
SELECT @name AS Name, @age AS Age;

6. 不快取程序（WITH RECOMPILE）

CREATE PROC proc_temp WITH RECOMPILE AS SELECT * FROM student;
-- 適用於參數嗅探嚴重、執行計畫不穩定的情境

7. 加密程序（WITH ENCRYPTION）

CREATE PROC proc_secret WITH ENCRYPTION AS SELECT * FROM salary;
-- 注意：加密後 sp_helptext 無法檢視，請妥善保管原始碼

8. 游標參數（OUTPUT CURSOR）

CREATE PROC proc_cursor @cur CURSOR VARYING OUTPUT
AS
    SET @cur = CURSOR FORWARD_ONLY STATIC FOR SELECT id, name, age FROM student;
    OPEN @cur;
GO

-- 呼叫端
DECLARE @exec_cur CURSOR, @id INT, @name VARCHAR(20), @age INT;
EXEC proc_cursor @cur = @exec_cur OUTPUT;
FETCH NEXT FROM @exec_cur INTO @id, @name, @age;
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT 'id: ' + CAST(@id AS VARCHAR) + ', name: ' + @name + ', age: ' + CAST(@age AS CHAR);
    FETCH NEXT FROM @exec_cur INTO @id, @name, @age;
END
CLOSE @exec_cur; DEALLOCATE @exec_cur;

9. 分頁程序（ROW_NUMBER）

CREATE PROC pro_stu @pageIndex INT, @pageSize INT
AS
    DECLARE @startRow INT = (@pageIndex - 1) * @pageSize + 1;
    DECLARE @endRow INT = @startRow + @pageSize - 1;
    SELECT * FROM (
        SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS rn FROM student
    ) t WHERE t.rn BETWEEN @startRow AND @endRow;
GO
EXEC pro_stu 2, 2;  -- 第 2 頁，每頁 2 筆

10. 錯誤處理 RAISERROR

-- 自訂錯誤訊息，嚴重等級 16（使用者可更正）
RAISERROR('庫存不足，無法下單', 16, 1);

-- 使用 sysmessages 系統訊息
RAISERROR(33003, 16, 1);  -- 33003 為自訂訊息編號

• severity 0-18：所有使用者可指定。
• severity 19-25：需 sysadmin 或 ALTER TRACE 權限，且必須加 WITH LOG。

效能與維護建議

1. 避免 SELECT *：明確列出欄位，減少網路傳輸並利用覆蓋索引。
2. 善用 SET NOCOUNT ON：關閉「受影響的行數」訊息，降低客戶端處理負擔。
3. 參數嗅探對策：OPTION (RECOMPILE)、OPTIMIZE FOR UNKNOWN、區域變數重新指派。
4. 版本控管：將 CREATE/ALTER PROC 腳本納入 Git，嚴禁直接在 SSMS 修改生產庫。