預存程序（Stored Procedure）是資料庫開發的核心議題之一。隨著 ORM、微服務、雲端原生架構興起，其角色與評價也在演進。本文系統整理優缺點，並針對現代開發情境給出架構決策建議。
優點
- 封裝複雜商業邏輯：將多步驟 T-SQL 封裝為單一呼叫單元，降低應用層複雜度。
- 執行計畫快取與重用：首次編譯產生計畫並快取。註：SQL Server 2005+ 所有參數化批次（含 ad-hoc）皆有計畫快取，優勢縮減但仍存在。
- 降低網路流量：僅傳送
EXEC proc 而非大量 T-SQL 文字，高延遲網路效果顯著。
- RPC 協定優化計畫匹配：參數以二進位傳遞，引擎易精確匹配快取計畫，避免字串比對失誤。
- 維護彈性：修改預存程序無需重新編譯/部署應用程式組件（前提：介面不變）。
- 程式碼重用與一致性：多應用、多模組共用同一預存程序，保證邏輯一致。
- 版本控管友善：腳本檔案可納入 Git/SVN，支援差異比對、回滾、Code Review。
- 安全性強化：
- 授權精細至預存程序層級，不需直接給資料表權限。
- 參數化天然防禦 SQL Injection（但動態 SQL 內部仍需參數化）。
SqlParameter 強型別驗證作為纏繞防線。
缺點
- 介面變更成本高：增減參數、變更回傳結果集結構，仍需同步修改應用層呼叫代碼（DataReader/GetValue、ORM Mapping 等）。
- 可移植性差：綁定 T-SQL / SQL Server 特有語法，異構資料庫遷移困難。跨雲端、多資料庫產品架構下為劣勢。
- 缺乏物件導向抽象：無繼承、介面、多型、DI 容器整合，難建構可重用領域模型層。大量業務邏輯下沉易形成「巨石預存程序」，維護噩夢。
- 可讀性與除錯體驗較差：T-SQL 缺乏現代 IDE 重構、單元測試、中斷點除錯支援（雖 SSMS / VS 有改善，仍不如 C#/Java）。
- 版本控管衝突：多人並行修改同一預存程序易產生合併衝突，缺乏程式碼層級的模組化隔離。
- 效能調校黑盒化：計畫快取參數嗅探、統計資料過期等問題排查需深厚 DBA 技能，應用開發者難介入。
現代架構決策矩陣
| 情境 | 建議 | 理由 |
|---|
| 高效能、大量資料、就近運算（ETL、報表彙總、批次處理） | ✅ 大量使用預存程序 | 就近處理資料，減少網路傳輸，發揮引擎並行查詢優勢 |
| 核心交易邏輯、一致性關鍵、頻繁變更 | ⚠️ 謹慎使用，搭配完整測試 | 優先考慮應用層領域模型 + 交易腳本，預存程序僅封裝極少數關鍵原子操作 |
| 跨資料庫、雲端原生、多租戶、CI/CD 密集 | ❌ 避免業務邏輯下沉 | 可移植性、測試性、版本控管、開發者體驗皆為劣勢 |
| 舊系統重構、漸進式遷移 | 🔄 保留既有，新功能寫應用層 | Strangler Fig 模式逐步剝離，避免大爆炸重寫 |
最佳實務檢核清單（若決定使用）
- ☐ 介面設計：單一職責、參數強型別、明確回傳碼/結果集結構
- ☐ 命名規範：
usp__（如 usp_Order_Create）
- ☐ 參數化動態 SQL：絕對禁止字串串接，全程
sp_executesql + 參數
- ☐ 錯誤處理：
TRY...CATCH + THROW / RAISERROR，記錄錯誤內容
- ☐ 交易控制：明確
BEGIN/COMMIT/ROLLBACK，避免巢狀交易陷阱
- ☐ 部署腳本：冪等部署（
IF EXISTS DROP / CREATE OR ALTER），納入 CI/CD Pipeline
- ☐ 單元測試：tSQLt 框架編寫預存程序單元測試
- ☐ 文件化：Extended Properties / Data Dictionary 記錄用途、參數、回傳值、副作用
預存程序非銀彈，亦非洪水猛獸。關鍵在於識別「資料密集型、就近運算、穩定介面」的核心場景，並在架構層級畫清邊界：資料存取層用預存程序，業務領域層用程式碼。
