批次指令碼有兩大缺點:無法傳參數、無法模組化重複呼叫。預存程序解決這兩點,並將邏輯下沉至資料庫層,減少網路傳輸、提升效能與資料完整性。
sp_ 開頭 (如 sp_help),儲於 master。xp_ 開頭 (如 xp_cmdshell)。
-- 建立
CREATE PROC proc_name
AS
BEGIN
-- 批次陳述式
END
GO
-- 修改 (CREATE → ALTER)
ALTER PROC proc_name
AS
BEGIN ... END
GO
-- 刪除
DROP PROC proc_name;
注意:預存程序本體不可包含 GO (批次分隔符),否則會提前結束建立。若需執行另一批次,請另建 SP 並呼叫。
CREATE PROC insUser
AS
BEGIN
BEGIN TRAN;
DECLARE @username VARCHAR(20);
SET @username = CONVERT(VARCHAR(8), GETDATE(), 112)
+ REPLACE(CONVERT(VARCHAR(10), GETDATE(), 8), ':', '');
IF NOT EXISTS (SELECT * FROM yonghu WHERE yonghuming = @username)
INSERT INTO yonghu VALUES (@username, '111111', '@163.com', '新用戶');
COMMIT TRAN;
END
GO
EXEC insUser; -- 同一秒內重複執行只會插入一筆 (主鍵/唯一約束)
CREATE PROC queryItem
@tablename NVARCHAR(10),
@id VARCHAR(20),
@idvalue INT
AS
DECLARE @sql VARCHAR(100);
SET @sql = 'SELECT * FROM ' + @tablename + ' WHERE ' + @id + ' = ' + CAST(@idvalue AS VARCHAR);
EXEC(@sql);
GO
-- 呼叫
EXEC queryItem 'yiren', 'yirenid', 10;
EXEC queryItem @tablename='jingjiren', @id='jingjirenid', @idvalue=1;
CREATE PROC returnProc
AS
BEGIN
BEGIN TRAN;
DECLARE @err INT = 0;
INSERT INTO yiren (xingming) VALUES ('王美麗');
SET @err += @@ERROR;
INSERT INTO yiren (yirenid) VALUES (1); -- 故意違反 PK
SET @err += @@ERROR;
IF @err > 0 ROLLBACK TRAN ELSE COMMIT TRAN;
RETURN @err; -- 0=成功, 非0=失敗
END
GO
-- 呼叫捕捉回傳碼
DECLARE @rc INT;
EXEC @rc = returnProc;
PRINT @rc;
CREATE PROC queryProfile
@id INT,
@xingming VARCHAR(50) OUTPUT,
@nianling INT OUTPUT
AS
SELECT @xingming = xingming, @nianling = nianling FROM yiren WHERE yirenid = @id;
GO
-- 呼叫端
DECLARE @name VARCHAR(50), @age INT;
EXEC queryProfile 1, @name OUTPUT, @age OUTPUT;
PRINT '姓名:' + @name + ', 年齡:' + CAST(@age AS VARCHAR);
原理:呼叫端變數傳址給 SP,SP 修改即直接反映呼叫端變數。
CREATE PROC selectProc
AS
SELECT * FROM yiren;
GO
EXEC selectProc; -- SSMS 顯示網格 / ADO.NET 填充 DataTable
此模式實現「帶參數的檢視表」。
| 選項 | 語法 | 用途 |
|---|---|---|
| WITH RECOMPILE | CREATE PROC ... WITH RECOMPILE | 每次執行重新編譯,解決參數嗅探導致計畫不穩 |
| WITH ENCRYPTION | CREATE PROC ... WITH ENCRYPTION | 隱藏定義原始碼 (sp_helptext 顯示加密) |
SSMS 物件總管 → 可程式性 → 預存程序 → 右鍵「新增預存程序」 → 選單「查詢」→「指定範本參數值」 (Ctrl+Shift+M) 填寫 → 編輯 → F5 執行建立。
預存程序是 T-SQL 程式設計的核心單元。掌握 輸入參數 實現通用邏輯、OUTPUT 參數 回傳多型別資料、RETURN 回傳執行狀態碼、結果集 供上層消費、WITH RECOMPILE/ENCRYPTION 控制編譯與安全性,即能構建健壯的資料存取層。