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作为参数的默认值。