您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 預存程序語法完整解析：CREATE PROCEDURE 參數與選項詳解

編輯：關於SqlServer

語法結構

CREATE PROC[EDURE] procedure_name [ ; number ]
    [ { @parameter data_type } 
        [ VARYING ] [ = default ] [ OUTPUT ]
    ] [ ,...n ]
[ WITH { RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION } ]
[ FOR REPLICATION ]
AS sql_statement [ ...n ]

關鍵參數詳細說明

元素	說明
`procedure_name`	預存程序名稱，符合識別碼規則。區域暫存：`#name`（僅目前工作階段可見）。全域暫存：`##name`（所有工作階段可見）。完整名稱含 `#` 不超過 128 字元。
`;number`	選用整數，將同名程序分組，便於一次 `DROP PROCEDURE name` 刪除整組。例：`orderproc;1`、`orderproc;2`。
`@parameter`	參數名稱，以 `@` 開頭，符合識別碼規則。最多 2,100 參數。僅能替代常數，不可替代表名、欄位名等物件名稱。
`data_type`	參數資料類型。所有類型皆可用，但 `cursor` 僅限 `OUTPUT` 參數，且必須同時指定 `VARYING`。
`VARYING`	指定輸出參數為動態結果集（僅游標參數適用）。
`default`	參數預設值（常數或 NULL）。若含 `LIKE` 可使用萬用字元（%、_、[]、[^]）。
`OUTPUT`	標示為輸出參數，可將值回傳給呼叫端。`text`、`ntext`、`image` 亦可為 OUTPUT。
`WITH RECOMPILE`	不快取執行計畫，每次執行重新編譯。適用參數值變化大、避免參數嗅探問題。
`WITH ENCRYPTION`	加密 `syscomments` 中的程式碼，防止被複寫發佈。
`FOR REPLICATION`	標示為複寫用程序，不可直接由使用者執行。

實用範例

1. 基本帶輸入/輸出參數

CREATE PROCEDURE dbo.GetOrderTotal
    @OrderID INT,
    @TotalAmount MONEY OUTPUT
AS
BEGIN
    SELECT @TotalAmount = SUM(UnitPrice * Quantity)
    FROM dbo.OrderDetails
    WHERE OrderID = @OrderID;
END;
GO

2. 預設值與選用參數

CREATE PROCEDURE dbo.SearchProducts
    @CategoryID INT = NULL,
    @Keyword NVARCHAR(50) = '%'
AS
BEGIN
    SELECT ProductID, ProductName, UnitPrice
    FROM dbo.Products
    WHERE (@CategoryID IS NULL OR CategoryID = @CategoryID)
      AND ProductName LIKE @Keyword;
END;
GO

3. 使用 WITH RECOMPILE 解決參數嗅探

CREATE PROCEDURE dbo.GetRecentOrders
    @CustomerID NCHAR(5),
    @DaysBack INT = 30
WITH RECOMPILE
AS
BEGIN
    SELECT OrderID, OrderDate
    FROM dbo.Orders
    WHERE CustomerID = @CustomerID
      AND OrderDate >= DATEADD(DAY, -@DaysBack, GETDATE());
END;
GO