SQL Server 存儲過程全維度指南：語法、參數、類型、C# 調用、系統變量

什麼是存儲過程？

存儲過程是經編譯後存儲在數據庫中的 T-SQL 代碼塊，接受參數、執行業務邏輯、返回結果。三大核心優勢：

1. 性能：預編譯緩存計劃，減少重複解析編譯開銷。
2. 網絡：一次調用替代多條 SQL 往返，降低延遲。
3. 安全：權限控制在過程級，防 SQL 注入，實現數據訪問最小權限原則。

創建語法全景

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(UserID, UserName, PassWord, RegisterTime, RegisterIP)。

1. 單一結果集

CREATE PROC GetUserAccount AS SELECT * FROM UserAccount;

2. 無參數、無返回、純執行（如插入）

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

3. 有 RETURN 值（影響行數）

CREATE PROC InUserAccountRe AS
INSERT UserAccount(...) VALUES (10,10,'2013-01-02',10);
RETURN @@ROWCOUNT;

4. 輸入 + 輸出參數

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

5. RETURN + 輸入 + 輸出

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

6. 輸出參數 + 結果集 + RETURN

CREATE PROC GetUserAccountRe2
    @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;

7. 多結果集

CREATE PROC GetUserAccountRe3 AS
SELECT * FROM UserAccount;
SELECT * FROM UserAccount WHERE UserID > 5;

C# 統一調用模式

private SqlCommand CreateCmd(string spName)
{
    var cmd = new SqlCommand(spName, _conn) { CommandType = CommandType.StoredProcedure };
    return cmd;
}

// 1. 單結果集 → SqlDataAdapter.Fill(DataSet)
public void RunGetUserAccount()
{
    using (var da = new SqlDataAdapter(CreateCmd("GetUserAccount")))
    {
        var ds = new DataSet(); da.Fill(ds); rpt.DataSource = ds; rpt.DataBind();
    }
}

// 2. 無參執行 → ExecuteNonQuery
public void RunInUserAccount()
{
    using (_conn) { _conn.Open(); lbl.Text = CreateCmd("InUserAccount").ExecuteNonQuery().ToString(); }
}

// 3. 獲取 RETURN 值
public void RunInUserAccountRe()
{
    var cmd = CreateCmd("InUserAccountRe");
    var pRet = new SqlParameter("rval", SqlDbType.Int) { Direction = ParameterDirection.ReturnValue };
    cmd.Parameters.Add(pRet);
    _conn.Open(); cmd.ExecuteNonQuery(); _conn.Close();
    lbl.Text = $"{pRet.Value}";  // 返回值
}

// 4. 輸入/輸出參數
public void RunGetUserAccountRe()
{
    var cmd = CreateCmd("GetUserAccountRe");
    cmd.Parameters.AddWithValue("@UserName", "7");
    var pOut = new SqlParameter("@UserID", SqlDbType.Int) { Direction = ParameterDirection.Output };
    cmd.Parameters.Add(pOut);
    _conn.Open(); cmd.ExecuteNonQuery(); _conn.Close();
    lbl.Text = pOut.Value.ToString();
}

// 5. RETURN + 輸入 + 輸出
public void RunGetUserAccountRe1()
{
    var cmd = CreateCmd("GetUserAccountRe1");
    cmd.Parameters.AddWithValue("@UserName", "7");
    cmd.Parameters.Add("@UserID", SqlDbType.Int).Direction = ParameterDirection.Output;
    cmd.Parameters.Add("rval", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
    _conn.Open(); cmd.ExecuteNonQuery(); _conn.Close();
    lbl.Text = $"Out:{cmd.Parameters["@UserID"].Value} Ret:{cmd.Parameters["rval"].Value}";
}

// 6. 輸出參數 + 結果集 + RETURN
public void RunGetUserAccountRe2()
{
    var cmd = CreateCmd("GetUserAccountRe2");
    cmd.Parameters.AddWithValue("@UserName", "7");
    cmd.Parameters.Add("@UserID", SqlDbType.Int).Direction = ParameterDirection.Output;
    cmd.Parameters.Add("rval", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
    _conn.Open(); cmd.ExecuteNonQuery();
    var ds = new DataSet(); new SqlDataAdapter(cmd).Fill(ds);
    rpt.DataSource = ds; rpt.DataBind(); _conn.Close();
    lbl.Text = $"Out:{cmd.Parameters["@UserID"].Value} Ret:{cmd.Parameters["rval"].Value}";
}

// 7. 多結果集 → DataSet.Tables[0], Tables[1]...
public void RunGetUserAccountRe3()
{
    var ds = new DataSet();
    new SqlDataAdapter(CreateCmd("GetUserAccountRe3")).Fill(ds);
    rpt1.DataSource = ds.Tables[0].DefaultView; rpt1.DataBind();
    rpt2.DataSource = ds.Tables[1].DefaultView; rpt2.DataBind();
}

常用系統全局變量速查

總結

存儲過程是 SQL Server 開發的基石。掌握參數方向（Input/Output/ReturnValue）、結果集處理（DataAdapter/Reader）、事務與錯誤控制，配合 using 模式管理連接生命週期，即可構建高性能、可維護的數據訪問層。