SQL Server 預存程序建立完整教學：從入門到進階範例

預存程序優勢

• 模組化重用：建立一次，多處呼叫，修改不影響應用程式碼
• 預編譯效能：首次執行產生執行計畫快取，後續直接複用
• 降低網路流量：僅傳遞呼叫指令而非大量 T-SQL 文字
• 安全性：可只授予 EXEC 權限，隱藏底層資料表細節

系統預存程序速查

EXEC sp_databases;           -- 列出所有資料庫
EXEC sp_tables;              -- 列出所有資料表
EXEC sp_columns 'student';   -- 檢視資料表欄位
EXEC sp_helpIndex 'student'; -- 檢視索引
EXEC sp_helpConstraint 'student'; -- 檢視約束
EXEC sp_stored_procedures;   -- 列出所有預存程序
EXEC sp_helptext 'sp_name';  -- 檢視建立腳本
EXEC sp_rename 'old', 'new'; -- 重命名表/索引/欄位
EXEC sp_renamedb 'oldDB', 'newDB'; -- 重命名資料庫

使用者自定義預存程序語法

CREATE PROC | PROCEDURE proc_name
    [@param datatype [= default] [OUTPUT], ...]
AS
    SQL_statements

六大實戰範例

1. 無參數查詢

CREATE PROC proc_get_student AS SELECT * FROM student;
EXEC proc_get_student;

2. 帶參數篩選

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

3. 萬用字元參數 (預設值)

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

4. 輸出參數回傳資料

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;

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

5. 不快取 (WITH RECOMPILE)

CREATE PROC proc_temp WITH RECOMPILE AS SELECT * FROM student;

6. 加密定義 (WITH ENCRYPTION)

CREATE PROC proc_enc WITH ENCRYPTION AS SELECT * FROM student;
-- sp_helptext 將無法檢視內容

進階主題

• 游標參數：@cur CURSOR VARYING OUTPUT 回傳結果集游標
• 分頁預存程序：搭配 ROW_NUMBER() 實現高效分頁
• RAISERROR：自訂錯誤訊息、嚴重等級 (0-25)、狀態碼，配合 TRY...CATCH 錯誤處理