SQL Server存儲過程參數

在上一篇教程中，學習了如何創建包裝SELECT語句的簡單存儲過程。 當調用此存儲過程時，它只是運行查詢並返回結果集。

在本教學中，我們將擴展存儲過程，用於將一個或多個值傳遞給它。 存儲過程的結果將根據參數的值進行更改。

使用一個參數創建存儲過程

以下查詢從示例資料庫中的products表返回產品列表：

SELECT
    product_name,
    list_price
FROM
    production.products
ORDER BY
    list_price;

可以使用CREATE PROCEDURE語句創建包裝此查詢的存儲過程：

CREATE PROCEDURE uspFindProducts
AS
BEGIN
    SELECT
        product_name,
        list_price
    FROM
        production.products
    ORDER BY
        list_price;
END;

但是，這次可以向存儲過程添加一個參數，以查找價格大於輸入價格的產品：

ALTER PROCEDURE uspFindProducts(@min_list_price AS DECIMAL)
AS
BEGIN
    SELECT
        product_name,
        list_price
    FROM
        production.products
    WHERE
        list_price >= @min_list_price
    ORDER BY
        list_price;
END;

在這個例子中：

• 首先，在uspFindProducts存儲過程中添加了一個名為@min_list_price的參數。 每個參數都必須以@符號開頭。 AS DECIMAL關鍵字指定@min_list_price參數的數據類型。參數必須由開括弧括起來。
• 其次，在SELECT語句的WHERE子句中使用@min_list_price參數來過濾價格大於或等於@min_list_price的產品。

創建使用一個參數的存儲過程

要執行uspFindProducts存儲過程，請將參數傳遞給它，如下所示：

EXEC uspFindProducts 1999;

執行上面查詢語句，得到以下結果：

存儲過程返回價格大於或等於1999的所有產品。

如果將參數更改為4999，將獲得不同的結果集：

EXEC uspFindProducts 4999;

執行上面查詢語句，得到以下結果：

創建使用多個參數的存儲過程

存儲過程可以採用一個或多個參數，參數以逗號分隔。

以下語句通過向其添加另一個名為@max_list_price的參數來修改uspFindProducts存儲過程：

ALTER PROCEDURE uspFindProducts(
    @min_list_price AS DECIMAL
    ,@max_list_price AS DECIMAL
)
AS
BEGIN
    SELECT
        product_name,
        list_price
    FROM
        production.products
    WHERE
        list_price >= @min_list_price AND
        list_price <= @max_list_price
    ORDER BY
        list_price;
END;

成功修改存儲過程後，可以通過傳遞兩個參數來執行它，一個用於@min_list_price，另一個用於@max_list_price，此存儲過程用於查詢在指定價格區間的產品資訊：

EXECUTE uspFindProducts 1999, 2400;

執行上面查詢語句，得到以下結果：

使用命名參數

如果存儲過程具有多個參數，則使用命名參數執行存儲過程會更好更清晰。

例如，以下語句使用命名參數@min_list_price和@max_list_price執行uspFindProducts存儲過程：

EXECUTE uspFindProducts
    @min_list_price = 1999,
    @max_list_price = 2400;

存儲過程的結果是相同的，但聲明更明顯。

創建文本參數

以下語句將@name參數作為字串參數添加到存儲過程。

ALTER PROCEDURE uspFindProducts(
    @min_list_price AS DECIMAL
    ,@max_list_price AS DECIMAL
    ,@name AS VARCHAR(max)
)
AS
BEGIN
    SELECT
        product_name,
        list_price
    FROM
        production.products
    WHERE
        list_price >= @min_list_price AND
        list_price <= @max_list_price AND
        product_name LIKE '%' + @name + '%'
    ORDER BY
        list_price;
END;

在SELECT語句的WHERE子句中，添加了以下條件：

product_name LIKE '%' + @name + '%'

通過執行此操作，存儲過程將返回價格在最小和最大價格範圍內的產品，並且產品名稱還包含傳入的一段文本。

成功更改存儲過程後，可以按如下方式執行：

EXECUTE uspFindProducts
    @min_list_price = 4000,
    @max_list_price = 5999,
    @name = '優米';

在上面語句中，使用uspFindProducts存儲過程來查找價格在4000至5999範圍內且其名稱包含單詞“優米”的產品。

執行上面查詢語句，得到以下結果：

創建可選參數

執行uspFindProducts存儲過程時，必須傳遞與這三個參數對應的所有三個參數值。

SQL Server可指定參數的默認值，以便在調用存儲過程時，可以使用默認值跳過參數。

請參閱以下存儲過程：

ALTER PROCEDURE uspFindProducts(
    @min_list_price AS DECIMAL = 0
    ,@max_list_price AS DECIMAL = 999999
    ,@name AS VARCHAR(max)
)
AS
BEGIN
    SELECT
        product_name,
        list_price
    FROM
        production.products
    WHERE
        list_price >= @min_list_price AND
        list_price <= @max_list_price AND
        product_name LIKE '%' + @name + '%'
    ORDER BY
        list_price;
END;

在此存儲過程中，將指定@min_list_price參數的默認值為：0，指定@max_list_price參數的默認值為：999999。

編譯存儲過程後，可以執行它而不用將值傳遞給@min_list_price和@max_list_price參數：

EXECUTE uspFindProducts
    @name = '鳳凰';

執行上面語句，得到以下結果：

使用NULL作為默認值

在uspFindProducts存儲過程中，使用999999作為默認的最大價格。 這不是很強大，因為將來有可能會有價格高於此值的產品。

避免這種情況的典型解決辦法是使用NULL作為參數的默認值：

ALTER PROCEDURE uspFindProducts(
    @min_list_price AS DECIMAL = 0
    ,@max_list_price AS DECIMAL = NULL
    ,@name AS VARCHAR(max)
)
AS
BEGIN
    SELECT
        product_name,
        list_price
    FROM
        production.products
    WHERE
        list_price >= @min_list_price AND
        (@max_list_price IS NULL OR list_price <= @max_list_price) AND
        product_name LIKE '%' + @name + '%'
    ORDER BY
        list_price;
END;

在WHERE子句中，更改了條件以處理@max_list_price參數的NULL值：

(@max_list_price IS NULL OR list_price <= @max_list_price)

以下語句執行uspFindProducts存儲過程以查找價格大於或等於500且名稱包含單詞""的產品。

EXECUTE uspFindProducts
    @min_list_price = 500,
    @name = '優米優';

執行上面查詢語句，得到以下結果：

在本教學中，學習了如何使用一個或多個參數創建和執行存儲過程。還學習了如何創建可選參數並使用NULL作為參數的默認值。