SQL Server 預存程序從入門到精通：參數、回傳值、條件邏輯與 .NET 呼叫實戰

為什麼使用預存程序？

• 執行計畫快取重用，減少編譯開銷。
• 網路傳輸量小（僅傳指令非完整 SQL）。
• 封裝業務邏輯，支援條件分支、交易控制。
• 安全性：授權執行預存程序，不需直接授權資料表權限。

基礎語法與分級範例

Level 1：最簡單查詢

CREATE PROCEDURE dbo.GetUsersDesc
AS
  SELECT userID FROM USERS ORDER BY userid DESC;
GO

.NET 呼叫 (SqlCommand)

using (var conn = new SqlConnection(connStr))
using (var cmd = new SqlCommand("GetUsersDesc", conn) { CommandType = CommandType.StoredProcedure })
{
  conn.Open();
  using (var dr = cmd.ExecuteReader())
    while (dr.Read()) { /* 處理資料 */ }
}

Level 2：輸入參數

CREATE PROCEDURE dbo.GetCommentsByDateRange
  @startDate VARCHAR(16), @endDate VARCHAR(16)
AS
  SELECT id FROM table_AX
  WHERE commentDateTime > @startDate AND commentDateTime < @endDate
  ORDER BY contentownerid DESC;
GO

using (var da = new SqlDataAdapter("GetCommentsByDateRange", conn))
{
  da.SelectCommand.CommandType = CommandType.StoredProcedure;
  da.SelectCommand.Parameters.AddWithValue("@startDate", startDate);
  da.SelectCommand.Parameters.AddWithValue("@endDate", endDate);
  da.Fill(ds);
}

Level 3：回傳執行狀態 (@@ERROR)

CREATE PROCEDURE dbo.UpdatePassword
  @passWord NVARCHAR(20), @userName NVARCHAR(20)
AS
  DECLARE @err INT;
  UPDATE WL_user SET password = @passWord WHERE UserName = @userName;
  SET @err = @@ERROR;
  SELECT @err AS err_code; -- 0 成功，非 0 失敗
GO

int result = (int)cmd.ExecuteScalar(); // 取得 err_code
if (result == 0) { /* 成功 */ }

Level 4：條件分支 (IF...BEGIN...END)

CREATE PROCEDURE dbo.GetCustomerCount
  @customerID INT
AS
  IF @customerID = -1
    BEGIN
      SELECT contentownerid, userCName, COUNT(*) AS cnt
      FROM view_usercomment
      GROUP BY contentownerid, userCName
      ORDER BY contentownerid DESC;
    END
  ELSE
    BEGIN
      SELECT contentownerid, userCName, COUNT(*) AS cnt
      FROM view_usercomment
      WHERE contentownerid = @customerID
      GROUP BY contentownerid, userCName
      ORDER BY contentownerid DESC;
    END
GO

進階主題

• OUTPUT 參數：@result INT OUTPUT，搭配 ParameterDirection.Output 取得單值。
• TRY...CATCH 錯誤處理：SQL Server 2005+ 支援結構化例外處理。
• 動態 SQL：sp_executesql 參數化動態執行，防注入並重用計畫。
• 暫存表 vs 資料表變數：依資料量與統計資料需求選擇（參考文章 96174）。

最佳實務檢查清單

1. 所有參數加上型別長度（VARCHAR(50) 非 VARCHAR）。
2. 明確指定 SET NOCOUNT ON 減少網路封包。
3. 交易範圍最小化，避免長鎖定。
4. 使用 sp_executesql 而非 EXEC('sql') 進行動態查詢。
5. 定期 sp_recompile 或更新統計資料維持計畫品質。

預存程序是資料庫開發的核心技能，熟練掌握參數化、錯誤處理、效能調優，能顯著提升應用系統的穩定度與擴展性。