SQL Server 預存程序編程實戰技巧與經驗總結

前言

MS SQL Server 隨著 2000 版本推出，在 Windows NT/2000 平台確立了統治地位。雖然僅能運行於微軟作業系統是其限制，但也促使其在平台上極致發揮效能。預存程序作為核心概念，合理使用可大幅提升效能與維護性：將商業邏輯封裝於伺服器端，變更時無需重新編譯部署客戶端程式。

適用版本

本文技巧若無特別註明，同時適用於 SQL Server 7.0 與 2000。

技巧 1：OUTPUT 參數的正確使用

一般預存程序回傳結果集，但若僅需回傳少數參數值，可使用 OUTPUT 參數：

CREATE PROCEDURE GetName
    @uid NVARCHAR(1),
    @username NVARCHAR(10) = '' OUTPUT
AS
    SET @username = 'hongchao'
GO

關鍵注意點： 在 SQL Server 2000 中，若預存程序只有一個參數且為 OUTPUT 類型，呼叫時必須給予初始值，否則會發生呼叫錯誤。這是從 7.0 到 2000 的行為變更。

技巧 2：避免關鍵字衝突的寫法習慣

系統關鍵字在版本間可能有差異。例如 level 在 SQL Server 7.0 的預存程序中可直接使用，但在 2000 中成為保留字導致錯誤：

-- SQL 7.0 正常，2000 報錯
SELECT * FROM users WHERE level = 1

-- 2000 正確寫法（加方括號）
SELECT * FROM users WHERE [level] = 1

最佳實務： 凡可能與系統關鍵字衝突的識別碼（欄位名、變數名、資料表名），統一使用 [] 包裹，確保跨版本遷移不出錯。

技巧 3：sp_executesql 中暫存表的可見性陷阱

在 sp_executesql 動態 SQL 中建立的區域暫存表 (#temp) 對呼叫端不可見，無法作為資料傳遞媒介。
解決： 使用全域暫存表 (##temp) ，其在工作階段層級可見，可跨呼叫層級傳遞資料。