10.2 SQL Server存储过程参数

 

SQL Server存储过程参数

 

目录

• SQL Server存储过程参数
  • 简介
  • 创建带一个参数的存储过程
  • 执行带一个参数的存储过程
  • 创建带多个参数的存储过程
  • 使用命名参数
  • 使用文本参数
  • 使用参数默认值
  • 使用NULL作为参数默认值
  • 存储过程输出参数
    • 创建输出参数
    • 执行带输出参数的存储过程

 

简介

在上一篇中中，知道如何创建一个简单的存储过程来包装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';

 

分类: SQL Server

标签: SQL Server , SQL Server高级 , 数据库