SQL Server 儲存程序完整指南：從建立到進階應用

儲存程序是 SQL Server 中封裝商務邏輯、提升效能與安全性的核心物件。本文系統整理其概念、優勢、系統程序與使用者自訂程序的完整語法範例，涵蓋參數傳遞、輸出參數、加密、游標、分頁與錯誤處理等進階主題。

什麼是儲存程序？

儲存程序是一組為完成特定功能而編譯儲存於資料庫中的 T-SQL 陳述式集合。使用者透過名稱與參數呼叫執行，可包含流程控制、資料操作，支援輸入/輸出參數、多重結果集與傳回值。因預先編譯並快取執行計畫，執行速度遠快於臨時批次；且呼叫端僅需傳遞程序名與參數，大幅降低網路流量。

四大核心優勢

• 模組化程式設計：一次建立、多處呼叫，修改程序不需動應用程式碼，提升可攜性。
• 高速執行：首次執行即最佳化並快取計畫，後續直接複用，避免重複編譯。
• 減輕網路負載：只傳呼叫指令，不傳大量 SQL 文字。
• 安全性機制：可針對程序授權，間接控制底層資料存取，防止未授權查詢。

系統儲存程序速查

系統程序儲於 master 資料庫，以 sp_ 開頭，可跨資料庫呼叫。常用範例如下：

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

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

1. 基本建立語法

CREATE PROC | PROCEDURE 程序名
    [@參數 資料類型 [=預設值] [OUTPUT], ...]
AS
    SQL_陳述式;

2. 無參數程序

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;

3. 修改程序

ALTER PROC proc_get_student
AS
    SELECT * FROM student;

4. 輸入參數程序

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;

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

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%';     -- 傳入自訂萬用字元

6. 輸出參數與輸入輸出參數

CREATE PROC proc_getStudentRecord
    @id INT,
    @name VARCHAR(20) OUT,
    @age VARCHAR(20) OUTPUT
AS
    SELECT @name = name, @age = age FROM student WHERE id = @id;
GO
DECLARE @id INT = 7, @name VARCHAR(20), @temp VARCHAR(20) = '1';
EXEC proc_getStudentRecord @id, @name OUT, @temp OUTPUT;
SELECT @name, @temp;

7. 不快取重新編譯

CREATE PROC proc_temp
WITH RECOMPILE
AS
    SELECT * FROM student;

8. 加密程序定義

CREATE PROC proc_temp_encryption
WITH ENCRYPTION
AS
    SELECT * FROM student;

9. 使用游標參數

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;

10. 分頁查詢程序

-- 方式一：指定起迄列號
CREATE PROC pro_page @startIndex INT, @endIndex INT
AS
    SELECT COUNT(*) FROM product;
    SELECT * FROM (
        SELECT ROW_NUMBER() OVER (ORDER BY pid) AS rowId, * FROM product
    ) t WHERE t.rowId BETWEEN @startIndex AND @endIndex;
GO
EXEC pro_page 1, 4;

-- 方式二：頁碼與頁大小
CREATE PROC pro_stu @pageIndex INT, @pageSize INT
AS
    DECLARE @startRow INT = (@pageIndex - 1) * @pageSize + 1,
            @endRow INT = @startRow + @pageSize - 1;
    SELECT * FROM (
        SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS number FROM student
    ) t WHERE t.number BETWEEN @startRow AND @endRow;
GO
EXEC pro_stu 2, 2;

RAISERROR 自訂錯誤處理

語法：RAISERROR (msg_id | msg_str | @var, severity, state [, argument...] [WITH option...])

• severity 0-18 任何使用者可用，19-25 需 sysadmin 權限並加 WITH LOG。
• state 1-127，預設 1。

RAISERROR('is error', 16, 1);
RAISERROR(33003, 16, 1);  -- 使用系統訊息