SQL Server 預存程序核心優勢與實戰範例：從預編譯到權限控制

預存程序是 SQL Server 封裝業務邏輯、提升效能、強化安全的核心機制。本文條列四大優勢 (預編譯、降低網路傳輸、程式碼重用、權限隔離)，並以倉庫存貨查詢為例，展示從動態 SQL 重構為參數化預存程序的完整過程。

四大核心優勢

實戰重構範例：倉庫存貨查詢

重構前 (動態 SQL，弊端明顯)

-- 客戶端程式碼 (如 ASP.NET, Java)
string sql = "SELECT Product, Quantity FROM Inventory WHERE Warehouse = '" + txtWarehouse.Text + "'";
cmd.CommandText = sql;  -- 每次重新編譯、傳輸長 SQL、需表權限、有注入風險

重構後 (參數化預存程序)

-- 1. 建立 SP
CREATE PROCEDURE sp_GetInventory
  @location VARCHAR(10)
AS
BEGIN
  SELECT Product, Quantity
  FROM Inventory
  WHERE Warehouse = @location;
END; GO

-- 2. 客戶端呼叫
EXEC sp_GetInventory 'FL';  -- 或 EXEC sp_GetInventory 'NY'

範例資料與執行結果

呼叫 EXEC sp_GetInventory 'FL' 回傳 Peas 200、Tomatoes 80、Watermelon 85。

進階提示

• 輸出參數 / 回傳值：單一純量結果用 RETURN @int 或 @param OUTPUT，避免結果集開銷。
• SET NOCOUNT ON：抑制「影響 n 筆」訊息，減少網路封包。
• 錯誤處理：SQL 2005+ 使用 TRY...CATCH 區塊統一異常處理。
• 避免過度封裝：極簡單、極少呼叫的查詢無需強制做 SP，維護成本需權衡。