儲存程序是 SQL Server 封裝業務邏輯、提升效能、強化安全的核心物件。本文系統講解語法結構、參數模式(IN/OUT/INOUT)、返回值、動態 SQL、權限模型與效能優勢,附完整實戰範例。
預先編譯、儲存在資料庫中的 T-SQL 批次,接受參數、執行邏輯、返回結果/狀態碼。類比程式語言的「方法/函數」,但運行在資料庫引擎內部。
| 優勢 | 說明 |
|---|---|
| 效能 | 執行計畫快取重用、減少網路往返、批次處理減少編譯開銷 |
| 安全 | 用戶無需表直接權限,僅授予 EXECUTE 權限;防 SQL 注入(參數化) |
| 完整性 | 封裝多步操作為原子單元,配合事務保證一致性 |
| 維護 | 業務邏輯集中資料庫層,應用層解耦,便於版本控制與熱修復 |
| 網路 | 只傳呼叫指令與參數/結果集,大幅降低頻寬 |
sp_ 前綴,儲於 master,所有資料庫可見(如 sp_help, sp_who, sp_configure)xp_ 前綴,呼叫外部 DLL(如 xp_cmdshell),高風險、慎用usp_ 或 proc_ 前綴EXEC sp_databases; -- 列出資料庫
EXEC sp_tables; -- 列出表
EXEC sp_columns 'table_name'; -- 列出欄位
EXEC sp_helpindex 'table_name'; -- 列出索引
EXEC sp_helpconstraint 'table_name'; -- 列出約束
EXEC sp_helptext 'proc_name'; -- 查看程序定義
EXEC sp_stored_procedures; -- 列出所有程序
EXEC sp_rename 'old_tbl', 'new_tbl'; -- 重命名表
EXEC sp_renamedb 'old_db', 'new_db'; -- 重命名資料庫
EXEC sp_defaultdb 'login', 'db'; -- 修改登入預設資料庫
EXEC sp_helpdb; -- 資料庫資訊
EXEC sp_attach_db / sp_detach_db; -- 附加/分離資料庫
CREATE PROCEDURE [schema.]proc_name
@param1 datatype [= default] [OUTPUT],
@param2 datatype [= default] [OUTPUT]
AS
BEGIN
SET NOCOUNT ON; -- 抑制 DONE_IN_PROC 訊息,提升效能
-- T-SQL 陳述式
RETURN int_status; -- 可選,僅 INT 型別
END;
-- 1. 僅輸入參數
CREATE PROC usp_GetOfficeName @Id INT
AS SELECT Name FROM Office_Info WHERE Id = @Id;
GO
EXEC usp_GetOfficeName 2;
-- 2. 輸出參數
CREATE PROC usp_GetOfficeNameOut @Id INT, @Name VARCHAR(50) OUTPUT
AS SELECT @Name = Name FROM Office_Info WHERE Id = @Id;
GO
DECLARE @n VARCHAR(50);
EXEC usp_GetOfficeNameOut 4, @n OUTPUT;
SELECT @n;
-- 3. 輸入輸出參數
CREATE PROC usp_IncCounter @Counter INT OUTPUT
AS SET @Counter = @Counter + 1;
GO
DECLARE @c INT = 10;
EXEC usp_IncCounter @c OUTPUT; -- @c 變 11
-- 4. 返回值 (僅 INT)
CREATE PROC usp_CheckExists @Id INT
AS
BEGIN
IF EXISTS (SELECT 1 FROM Office_Info WHERE Id = @Id) RETURN 1;
RETURN -1;
END;
GO
DECLARE @ret INT;
EXEC @ret = usp_CheckExists 2;
PRINT @ret;
CREATE PROC usp_DynamicQuery @TableName SYSNAME, @Col SYSNAME, @Val VARCHAR(100)
AS
BEGIN
DECLARE @sql NVARCHAR(MAX) = N'SELECT * FROM ' + QUOTENAME(@TableName) +
N' WHERE ' + QUOTENAME(@Col) + N' = @val';
EXEC sp_executesql @sql, N'@val VARCHAR(100)', @val = @Val;
-- sp_executesql 參數化 → 計畫重用、防注入
END;
GRANT EXECUTE ON usp_Proc TO [user/role]; — 僅需執行權限EXECUTE AS CALLER/SELF/OWNER/'user' 控制執行上下文SET NOCOUNT ON:減少網路封包SELECT *,明確列出欄位sp_executesql)而非字串拼接OPTION (RECOMPILE) 解決參數嗅探問題sp_recompile 或 DBCC FREEPROCCACHE 更新計畫-- 查看程序定義
EXEC sp_helptext 'usp_ProcName';
-- 或
SELECT OBJECT_DEFINITION(OBJECT_ID('usp_ProcName'));
-- 監控執行統計
SELECT OBJECT_NAME(object_id) AS ProcName,
execution_count,
total_elapsed_time/execution_count AS AvgMs,
last_execution_time
FROM sys.dm_exec_procedure_stats
WHERE database_id = DB_ID()
ORDER BY AvgMs DESC;
儲存程序不是「過時技術」,而是**資料庫層服務化**的基石。合理使用能構建安全、高效、可維護的資料存取層。新專案建議:核心寫入路徑全走儲存程序,複雜查詢視圖/內聯函數/應用層 ORM 共存,各司其職。