前言

雖然 ORM 與 NoSQL 減少了 T-SQL 直接編寫，但存儲過程仍是高效能、高安全性資料庫應用的核心技術。本文系統總結 SQL Server 存儲過程的核心概念、優缺點、分類、語法詳解、進階特性與實戰案例。

一、什麼是存儲過程

存儲過程是一組為完成特定功能的 SQL 語句集合，經編譯後存儲在資料庫中。類似程式語言的方法，支援參數輸入/輸出、邏輯控制、事務管理，首次執行後計畫緩存，後續調用效能極高。

二、核心優勢

優勢	說明
標準組件化編程	創建一次、多處調用；DBA 可獨立優化 SQL，不影響應用程式碼
執行速度快	預編譯緩存執行計畫，避免重複解析優化；批次處理大量 T-SQL 顯著優於動態 SQL
減輕網路流量	客戶端僅傳遞呼叫指令，非多條 SQL 文本，降低網路負載
安全機制	可對單個存儲過程授權，實現細粒度存取控制，防止非授權資料存取

三、潛在缺點

• 簡單查詢無優勢：單條 SQL 使用存儲過程反而增加開銷
• 開發除錯困難：缺乏現代 IDE 支援，邏輯複雜時排查艱難
• 可移植性差：深度綁定 SQL Server 語法，異構遷移成本高

四、存儲過程分類與語法

1. 系統存儲過程

前綴 sp_，儲於 master 資料庫，提供系統管理功能：

-- 重命名表/欄位/索引
EXEC sp_rename 'stu', 'stud';
EXEC sp_rename 'stud.name', 'sName', 'column';
EXEC sp_rename N'student.idx_cid', N'idx_cidd', N'index';

-- 查詢所有存儲過程
SELECT * FROM sys.objects WHERE type = 'P';
SELECT * FROM sys.objects WHERE type_desc LIKE '%pro%' AND name LIKE 'sp%';

2. 自定義存儲過程

基本語法

CREATE PROC | PROCEDURE pro_name
    [{@參數 資料類型} [=預設值] [OUTPUT], ...]
AS
SQL_statements

無參數範例

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;

修改存儲過程

ALTER PROC proc_get_student
AS
SELECT * FROM student;

帶參數 (輸入/輸出/預設值)

-- 輸入參數
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;

-- 萬用字元預設值
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%'; -- 傳參

-- 輸出參數
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), @temp VARCHAR(20)='1';
EXEC proc_getStudentRecord @id, @name OUT, @temp OUTPUT;
SELECT @name, @temp;

進階特性

-- 不緩存執行計畫 (資料分佈變化大時)
CREATE PROC proc_temp WITH RECOMPILE AS SELECT * FROM student;

-- 加密存儲過程定義
CREATE PROC proc_temp_encryption WITH ENCRYPTION AS SELECT * FROM student;
-- sp_helptext 將無法查看加密過程內容

-- 分頁存儲過程 (ROW_NUMBER)
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 ASC) AS number FROM student
) t WHERE t.number BETWEEN @startRow AND @endRow;
EXEC pro_stu 2, 2;

五、錯誤處理：RAISERROR

RAISERROR({msg_id | msg_str | @local_var}, severity, state [,argument...] [WITH option...])

-- 自訂錯誤
RAISERROR('is error', 16, 1);

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

-- 嚴重級別說明：
-- 0-18: 任何使用者可指定
-- 19-25: 需 sysadmin 或 ALTER TRACE 權限，且需 WITH LOG

六、返回值兩種方式

方式	資料類型	適用場景
RETURN	僅 INT	狀態碼、簡單計數
OUTPUT 參數	任意類型	複雜資料、多值返回

-- .NET 調用 OUTPUT 參數範例
SqlParameter sp = new SqlParameter("@fid", SqlDbType.Int, 4);
sp.Direction = ParameterDirection.Output;
// 執行存儲過程後
return Convert.ToInt32(sp.Value.ToString());

七、實戰案例精選

7.1 工作流待辦事項分頁 (動態 SQL)

ALTER PROCEDURE [dbo].[PageWorkFlowToDo]
    @pageindex INT, @pagesize INT, @uid VARCHAR(200), @strWhere VARCHAR(500)
AS
BEGIN
    DECLARE @sql VARCHAR(4000);
    SET @sql = 'SELECT TOP ' + CAST(@pagesize AS VARCHAR) + ' ... 
        WHERE db_userwork.uid=' + @uid + ' AND db_userwork.State=1 
        AND db_userwork.uwid NOT IN (
            SELECT TOP ' + CAST((@pageindex-1)*@pagesize AS VARCHAR) + ' uwid ... ORDER BY IniStartTime DESC
        )';
    IF @strWhere <> '' SET @sql = @sql + @strWhere + ' ORDER BY IniStartTime DESC';
    ELSE SET @sql = @sql + ' ORDER BY IniStartTime DESC';
    EXEC(@sql);
END

7.2 遞迴查詢部門群組 (游標應用)

ALTER PROCEDURE [dbo].[GetGroupIDByOnLine] @GName VARCHAR(200)
AS
BEGIN
    SET NOCOUNT ON;
    CREATE TABLE #temp(GroupID INT);
    CREATE TABLE #tempTwoTable(GroupID INT);
    
    INSERT INTO #temp SELECT GId FROM g_group WHERE PId = (SELECT GId FROM g_group WHERE GName=@GName);
    
    IF (SELECT COUNT(*) FROM #temp) > 0
    BEGIN
        DECLARE @GroupID INT, cur CURSOR FOR SELECT GroupID FROM #temp;
        OPEN cur; FETCH NEXT FROM cur INTO @GroupID;
        WHILE @@FETCH_STATUS = 0
        BEGIN
            INSERT INTO #tempTwoTable SELECT GId FROM g_group WHERE PId = @GroupID;
            INSERT INTO #tempTwoTable SELECT @GroupID;
            FETCH NEXT FROM cur INTO @GroupID;
        END
        CLOSE cur; DEALLOCATE cur;
        INSERT INTO #tempTwoTable SELECT GId FROM g_group WHERE GName=@GName;
    END
    ELSE INSERT INTO #tempTwoTable SELECT GId FROM g_group WHERE GName=@GName;
    
    -- 字串聚合輸出
    DECLARE @GroupIDs VARCHAR(100), @DouHao NVARCHAR(MAX)='', cur2 CURSOR FOR SELECT GroupID FROM #tempTwoTable;
    OPEN cur2; FETCH NEXT FROM cur2 INTO @GroupIDs;
    WHILE @@FETCH_STATUS = 0
    BEGIN SET @DouHao = @DouHao + CONVERT(VARCHAR(20), @GroupIDs) + ','; FETCH NEXT FROM cur2 INTO @GroupIDs; END
    CLOSE cur2; DEALLOCATE cur2;
    SELECT SUBSTRING(@DouHao, 1, LEN(@DouHao)-1);
    DROP TABLE #tempTwoTable, #temp;
END

7.3 SQL Server Agent 作業自動化

ALTER PROCEDURE [dbo].[add_job] @portal_dbName VARCHAR(200)
AS
BEGIN
    DECLARE @jobid UNIQUEIDENTIFIER, @jobname SYSNAME, @sysdatetime VARCHAR(500);
    SET @sysdatetime = CONVERT(VARCHAR(23), DATENAME(yyyy,GETDATE())) + ...; -- 組合時間戳
    SET @jobname = N'portal_safe_job' + @sysdatetime;
    
    IF EXISTS(SELECT * FROM msdb.dbo.sysjobs WHERE name=@jobname)
        EXEC msdb.dbo.sp_delete_job @job_name=@jobname;
    
    EXEC msdb.dbo.sp_add_job @job_name=@jobname, @job_id=@jobid OUTPUT;
    
    -- 定義作業步驟
    DECLARE @sql NVARCHAR(4000) = N'DELETE FROM db_userOnline WHERE hartTime < DATEADD(minute,-2,GETDATE()) OR logined=1';
    EXEC msdb.dbo.sp_add_jobstep @job_id=@jobid, @step_name=N'step1', @subsystem='TSQL', @database_name=@portal_dbName, @command=@sql;
    
    -- 定義排程 (每小時執行)
    EXEC msdb.dbo.sp_add_jobschedule @job_id=@jobid, @name=N'排程名稱', @freq_type=4, @freq_interval=1, @freq_subday_type=0x4, @freq_subday_interval=1;
    
    -- 目標伺服器
    EXEC msdb.dbo.sp_add_jobserver @job_id=@jobid, @server_name=N'(local)';
END

八、最佳實踐建議

1. 適度使用：複雜業務邏輯、批次處理、安全敏感操作優先考慮存儲過程
2. 參數化防注入：嚴禁動態 SQL 拼接使用者輸入，善用參數與 sp_executesql
3. 命名規範：採用 proc_功能模組_動作 格式，避免 sp_ 前綴 (系統保留)
4. 版本控管：存儲過程腳本納入 Git 管理，部署透過遷移腳本執行
5. 效能監控：定期檢視 sys.dm_exec_procedure_stats 識別慢存儲過程
6. 文件化：使用擴展屬性 (sp_addextendedproperty) 記錄用途、參數、修改歷史