10.2 SQL Server存储过程参数
SQL Server存储过程参数
简介
在上一篇中中，知道如何创建一个简单的存储过程来包装Select语句。当调用此存储过程时，它只需运行查询并返回结果集即可。
本篇将介绍如何向存储过程中传递一些参数，并在存储过程中使用这些参数，然后根据参数返回想要的结果。
创建带一个参数的存储过程
下面查询返回产品列表：
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的产品。
执行带一个参数的存储过程
返回价格大于等于100的产品：
EXEC uspFindProducts 100;
存储过程返回价格大于或等于100的所有产品。
如果将参数更改为200，将得到不同的结果：
EXEC uspFindProducts 200;
创建带多个参数的存储过程
存储过程可以接受一个或多个参数。参数之间用逗号分隔。
以下语句通过向uspFindProducts存储过程中再添加一个名为@max_list_price的参数来修改该存储过程：
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 900, 1000;
使用命名参数
如果存储过程有多个参数，那么使用命名参数执行存储过程会更好、更清晰。
例如，以下语句使用命名参数@min_list_price和@max_list_price执行uspFindProducts存储过程：
EXECUTE uspFindProducts
@min_list_price = 900,
@max_list_price = 1000;
执行结果相同，但是这样代码更清晰，通过执行代码就清晰知道参数的作用。
使用文本参数
下面的语句将@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;
在查询的WHERE子句中，添加了如下条件：
product_name LIKE '%' + @name + '%'
存储过程将返回价格在最小和最大价格范围内的产品，并且产品名称还包含传入的一段文本。
执行一下：
EXECUTE uspFindProducts
@min_list_price = 900,
@max_list_price = 1000,
@name = 'Trek';
在这条语句中，使用uspFindProducts存储过程查找价格在900和1000之间且名称包含Trek一词的产品。
使用参数默认值
执行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。
一旦编译过后，我们执行这个存储过程的时候就可以跳过这两个参数：
EXECUTE uspFindProducts
@name = 'Trek';
在这种情况下，存储过程在执行查询时，对@min_list_price参数使用0，对@max_list_price参数使用999999。
当然，也可以将参数传递给可选参数。例如，以下语句返回价格大于或等于6000且名称中包含单词Trek的所有产品：
EXECUTE uspFindProducts
@min_list_price = 6000,
@name = 'Trek';
使用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且名称包含单词Haro的产品：
EXECUTE uspFindProducts
@min_list_price = 500,
@name = 'Haro';
存储过程输出参数
创建输出参数
语法：
parameter_name data_type OUTPUT
存储过程可以有多个输出参数。此外，输出参数可以是任何有效的数据类型，例如整数、日期和可变字符串。
例如，以下存储过程按年款查找产品，并通过@product_count输出参数返回产品数量：
CREATE PROCEDURE uspFindProductByModel (
@model_year SMALLINT,
@product_count INT OUTPUT
) AS
BEGIN
SELECT
product_name,
list_price
FROM
production.products
WHERE
model_year = @model_year;
SELECT @product_count = @@ROWCOUNT;
END;
本存储过程中：
首先，我们创建了一个名为@product_count的输出参数，用于存储找到的产品数量：
@product_count INT OUTPUT
然后，在SELECT语句之后，将查询返回的行数（@@ROWCOUNT）分配给@product_count参数。
注意，
@@ROWCOUNT是一个系统变量，它返回前面语句返回的行数。
执行带输出参数的存储过程
要调用带输出参数的存储过程，有如下两部：
- 首先，声明变量以保存输出参数返回的值
- 其次，在存储过程调用时使用这些变量。
例如，以下语句执行前面创建的存储过程uspFindProductByModel：
DECLARE @count INT;
EXEC uspFindProductByModel
@model_year = 2018,
@product_count = @count OUTPUT;
SELECT @count AS 'Number of products found';
在这个例子中：
首先，声明@count变量以保存存储过程的输出参数的值：
DECLARE @count INT;
然后，执行uspFindProductByModel存储过程并传递参数：
EXEC uspFindProductByModel
@model_year = 2018,
@product_count = @count OUTPUT;
在该语句中，@model_year为2018，@count变量指定接受输出参数@product_count的值。
也可以如下简写调用方式：
EXEC uspFindProductByModel 2018, @count OUTPUT;
请注意，如果在@count变量之后忘记了OUTPUT关键字，那么@coount变量将为NULL。
最后打印出变量值查看结果：
SELECT @count AS 'Number of products found';
