SQL Server 存儲過程入門教程：概念、優點、類型、語法、創建調用實例、加密與重編譯選項

什麼是存儲過程？

將常用或復雜的 SQL 邏輯預編譯存儲在數據庫中，賦予一個名稱，調用時僅需 EXEC 過程名 即可自動執行。

與普通 SQL 對比優勢

三大類型

• 系統存儲過程：sp_ 前綴，管理/信息查詢（如 sp_help）
• 擴展存儲過程：xp_ 前綴，調用 OS 功能（如 xp_cmdshell）
• 用戶自定義：業務邏輯核心，本文重點

標準創建語法

CREATE PROCEDURE 過程名
  @參數名 數據類型 [OUTPUT],
  ...
[WITH {RECOMPILE | ENCRYPTION}]
AS
BEGIN
  -- SQL 語句
END;

• OUTPUT：標記輸出參數
• WITH RECOMPILE：每次執行重新編譯（應對參數嗅探）
• WITH ENCRYPTION：加密定義文本（sp_helptext 不可見）

實例：帶輸出參數的查詢

CREATE PROC upGetUserName
  @intUserId INT,
  @ostrUserName NVARCHAR(20) OUTPUT
AS
BEGIN
  SELECT @ostrUserName = uName FROM uUser WHERE uId = @intUserId;
END;

SSMS 生成的標準腳本（含 SET 選項）

SET ANSI_NULLS ON; GO
SET QUOTED_IDENTIFIER ON; GO
CREATE PROCEDURE sp_test
  @id INT,
  @name VARCHAR(20) OUTPUT
AS
BEGIN
  SET NOCOUNT ON;  -- 關鍵：抑制 DONE_IN_PROC 消息，提升性能
  SELECT @name = name FROM test WHERE id = @id;
END;
GO

調用方法

-- SSMS / sqlcmd
DECLARE @return_value INT, @name VARCHAR(20);
EXEC @return_value = dbo.sp_test @id = 1, @name = @name OUTPUT;
SELECT @name AS '@name';
SELECT 'Return Value' = @return_value;

應用代碼中調用

• ADO.NET：SqlCommand cmd = new SqlCommand("sp_test", conn) { CommandType = CommandType.StoredProcedure }; cmd.Parameters.Add("@id", SqlDbType.Int).Value = 1; var p = cmd.Parameters.Add("@name", SqlDbType.VarChar, 20); p.Direction = ParameterDirection.Output; cmd.ExecuteNonQuery(); string name = p.Value.ToString();
• JDBC：CallableStatement cs = conn.prepareCall("{? = call sp_test(?, ?)}"); cs.registerOutParameter(1, Types.INTEGER); cs.setInt(2, 1); cs.registerOutParameter(3, Types.VARCHAR); cs.execute();

新手避坑指南

1. 變量必須以 @ 開頭
2. OUTPUT 參數調用端必須註冊方向
3. 不要在事務中用 RETURN 強退（用 THROW 或 RAISERROR）
4. 首行加 SET NOCOUNT ON
5. 涉及 DDL 操作謹慎用 WITH RECOMPILE
6. 加密後無法通過 sp_helptext 查看，備份腳本

進階方向

• 表值參數（TVP）批量傳遞數據
• CLR 存儲過程（C#/VB.NET 編寫）
• 原生編譯存儲過程（In-Memory OLTP，SQL 2014+）
• 計劃指南與查詢存儲優化