SQL Server 預存程序完整指南：語法詳解、參數模式、多重結果集與 C# 呼叫實戰

預存程序是預先編譯的 T-SQL 程式碼區塊，封裝業務邏輯、減少網路往返、提升執行效率、強化安全性 (使用者僅需 EXECUTE 權限)。本文系統整理建立語法、參數選項、七大實戰範例模式與 C# 呼叫完整程式碼。

核心優勢

• 效能： 首次執行編譯產生計畫快取，後續重複使用避免重新編譯。
• 網路： 一條 `EXEC` 指令取代大量 T-SQL 文字傳輸。
• 安全： 使用者無需直接表權限，僅授 `EXECUTE` 即可間接存取資料。
• 維護： 邏輯集中資料庫端，應用程式無需重新部署即可修改業務規則。

建立語法與參數選項

CREATE PROC[EDURE] [schema.]procedure_name [;number]
    [ {@parameter data_type} [VARYING] [= default] [OUTPUT] ] [ ,...n ]
[ WITH { RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION } ]
[ FOR REPLICATION ]
AS
    sql_statement [ ...n ]

七大實戰範例模式 (以 UserAccount 表為例)

1. 單一結果集

CREATE PROC GetUserAccount AS SELECT * FROM UserAccount; GO
EXEC GetUserAccount;

2. 無參數、無回傳值 (純 DML)

CREATE PROC InsUserAccount AS 
    INSERT INTO UserAccount (UserName, PassWord, RegisterTime, RegisterIP) 
    VALUES (9, 9, '2013-01-02', 9); GO
EXEC InsUserAccount;

3. 回傳整數值 (RETURN)

CREATE PROC InsUserAccountRet AS 
    INSERT INTO UserAccount (...) VALUES (10, 10, '2013-01-02', 10);
    RETURN @@ROWCOUNT; GO
EXEC InsUserAccountRet;  -- 可在 C# 取得 ReturnValue

4. 輸入 + 輸出參數

CREATE PROC GetUserAccountOut @UserName NCHAR(20), @UserID INT OUTPUT AS
    IF @UserName > 5 SELECT @UserID = COUNT(*) FROM UserAccount WHERE UserID > 25
    ELSE SET @UserID = 1000; GO
DECLARE @Out INT; EXEC GetUserAccountOut '7', @Out OUTPUT; SELECT @Out;

5. 同時具備 RETURN、輸入、輸出

CREATE PROC GetUserAccountAll @UserName NCHAR(20), @UserID INT OUTPUT AS
    IF @UserName > 5 SELECT @UserID = COUNT(*) FROM UserAccount WHERE UserID > 25
    ELSE SET @UserID = 1000;
    RETURN @@ROWCOUNT; GO

6. 輸出參數 + 結果集

CREATE PROC GetUserAccountOutRs @UserName NCHAR(20), @UserID INT OUTPUT AS
    IF @UserName > 5 SELECT @UserID = COUNT(*) FROM UserAccount WHERE UserID > 25
    ELSE SET @UserID = 1000;
    SELECT * FROM UserAccount;
    RETURN @@ROWCOUNT; GO

7. 多重結果集

CREATE PROC GetMultiResults AS
    SELECT * FROM UserAccount;
    SELECT * FROM UserAccount WHERE UserID > 5; GO
EXEC GetMultiResults;  -- C# DataSet.Tables[0], Tables[1]

C# 完整呼叫範本 (ADO.NET)

public SqlCommand CreateCmd(string procName) {
    var cmd = new SqlCommand(procName, conn) { CommandType = CommandType.StoredProcedure };
    return cmd;
}

// 1. 單一結果集
var da = new SqlDataAdapter(CreateCmd("GetUserAccount"));
var ds = new DataSet(); da.Fill(ds); grid.DataSource = ds.Tables[0];

// 2. 無回傳值 DML
cmd = CreateCmd("InsUserAccount"); conn.Open(); 
lbl.Text = cmd.ExecuteNonQuery().ToString(); conn.Close();

// 3. 取得 RETURN 值
cmd = CreateCmd("InsUserAccountRet");
var pRet = new SqlParameter("ret", SqlDbType.Int) { Direction = ParameterDirection.ReturnValue };
cmd.Parameters.Add(pRet);
conn.Open(); cmd.ExecuteNonQuery(); conn.Close();
lbl.Text = pRet.Value.ToString();

// 4. 輸入/輸出參數
cmd = CreateCmd("GetUserAccountOut");
cmd.Parameters.Add("@UserName", SqlDbType.NChar, 20).Value = "7";
cmd.Parameters.Add("@UserID", SqlDbType.Int).Direction = ParameterDirection.Output;
conn.Open(); cmd.ExecuteNonQuery(); conn.Close();
lbl.Text = cmd.Parameters["@UserID"].Value.ToString();

// 5. 同時 RETURN + OUTPUT
cmd = CreateCmd("GetUserAccountAll");
cmd.Parameters.Add("@UserName", SqlDbType.NChar, 20).Value = "7";
cmd.Parameters.Add("@UserID", SqlDbType.Int).Direction = ParameterDirection.Output;
cmd.Parameters.Add("ret", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
conn.Open(); cmd.ExecuteNonQuery(); conn.Close();
// 讀取 cmd.Parameters["@UserID"].Value 及 cmd.Parameters["ret"].Value

// 6. 結果集 + OUTPUT
cmd = CreateCmd("GetUserAccountOutRs");
// ... 參數同 5 ...
var da2 = new SqlDataAdapter(cmd);
var ds2 = new DataSet(); da2.Fill(ds2);  // ds2.Tables[0] 為結果集

// 7. 多重結果集
var da3 = new SqlDataAdapter(CreateCmd("GetMultiResults"));
var ds3 = new DataSet(); da3.Fill(ds3);  // ds3.Tables[0], Tables[1]

常用系統全域變數速查

掌握預存程序參數模式與 C# 對應呼叫方式，即可構建高效、安全、可維護的資料存取層。