建立預存程序的三大構成要素
- 所有輸入參數與輸出參數
- 針對資料庫的操作陳述式 (含呼叫其他 SP)
- 回傳給呼叫端的狀態值 (成功/失敗)
方法一：Enterprise Manager 圖形化建立 (適合初學者)
- 啟動 Enterprise Manager，連線至目標伺服器
- 展開目標資料庫 → 右鍵 Stored Procedures → New Stored Procedure
- 輸入預存程序主體 (範本已含
CREATE PROCEDURE 框架)
- 點擊 Check Syntax 驗證語法
- 確定儲存
- 右鍵該 SP → All Tasks → Manage Permissions 設定執行權限
方法二：CREATE PROCEDURE T-SQL 指令 (開發者慣用)
語法規則完整版
CREATE PROC[EDURE] procedure_name [; number]
[ { @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ]
[ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION }
]
[ FOR REPLICATION ]
AS
sql_statement [ ...n ]
關鍵選項解析
| 選項 | 用途 |
|---|
@parameter data_type | 定義參數名稱、資料類型、預設值、OUTPUT 方向 |
VARYING | 僅適用游標輸出參數，標示結果集結構可變 |
WITH RECOMPILE | 每次執行重新編譯，適用參數敏感度高、計畫不穩定場景 |
WITH ENCRYPTION | 加密定義文本，sp_helptext 與 EM 皆無法檢視 (安全性考量) |
FOR REPLICATION | 標記為複寫用途，不可直接執行 |
實用範例：帶參數、輸出、錯誤處理
CREATE PROCEDURE usp_GetOrderSummary
@CustomerID NCHAR(5),
@OrderDateFrom DATETIME,
@OrderDateTo DATETIME = NULL, -- 預設值
@TotalAmount MONEY OUTPUT -- 輸出參數
WITH RECOMPILE
AS
BEGIN
SET NOCOUNT ON;
IF @OrderDateTo IS NULL SET @OrderDateTo = GETDATE();
SELECT
OrderID, OrderDate,
SUBTOTAL = (SELECT SUM(UnitPrice * Quantity * (1-Discount))
FROM [Order Details] od
WHERE od.OrderID = o.OrderID)
FROM Orders o
WHERE o.CustomerID = @CustomerID
AND o.OrderDate BETWEEN @OrderDateFrom AND @OrderDateTo;
SELECT @TotalAmount = SUM(UnitPrice * Quantity * (1-Discount))
FROM [Order Details] od
JOIN Orders o ON o.OrderID = od.OrderID
WHERE o.CustomerID = @CustomerID
AND o.OrderDate BETWEEN @OrderDateFrom AND @OrderDateTo;
RETURN 0; -- 成功狀態
END
建立時必須注意的限制
- 單一批次中
CREATE PROCEDURE 不能與其他 SQL 陳述式混用
- 資料庫擁有者 (dbo) 預設擁有建立權限，可授權他人
- 命名必須符合識別碼規則 (首字母、長度 1-128)
- 只能在**目前資料庫**建立屬於該資料庫的 SP
