指定存储过程中的参数
适用于：
SQL Server Azure SQL 数据库 Azure SQL 托管实例 Azure Synapse Analytics Analytics Platform System (PDW)
通过指定过程参数，调用程序可以将值传递给过程的主体。 在执行过程期间，这些值可以用于各种目的。 如果将参数标记为 OUTPUT 参数，则过程参数还可以将值返回给调用程序。
一个过程最多可以有 2100 个参数，每个参数都有名称、数据类型和方向。 还可以为参数指定默认值（可选）。
下面的章节提供有关将值传递给参数以及在过程调用期间如何使用每个参数属性的信息。
注意
请参阅本文练习的 AdventureWorks 系列示例数据库。 有关详细信息，请参阅 AdventureWorks sample databases（AdventureWorks 示例数据库）。
将值传递给参数
使用过程调用提供的参数值必须为常量或变量，不能将函数名称作为参数值。 变量可以是用户定义变量或系统变量，例如 @@spid。
下列示例演示如何将参数值传递给过程 uspGetWhereUsedProductID。 它们说明了如何将参数作为常量和变量进行传递，以及如何使用变量传递函数值。
USE AdventureWorks2022;
GO
-- Passing values as constants.
EXEC dbo.uspGetWhereUsedProductID 819, '20050225';
GO
-- Passing values as variables.
DECLARE @ProductID int, @CheckDate datetime;
SET @ProductID = 819;
SET @CheckDate = '20050225';
EXEC dbo.uspGetWhereUsedProductID @ProductID, @CheckDate;
GO
下面的示例返回错误，因为无法将函数作为参数值传递。
-- Try to use a function as a parameter value.
-- This produces an error message.
EXEC dbo.uspGetWhereUsedProductID 819, GETDATE();
请改用变量将函数值传递给参数，如以下示例所示：
-- Passing the function value as a variable.
DECLARE @CheckDate datetime;
SET @CheckDate = GETDATE();
EXEC dbo.uspGetWhereUsedProductID 819, @CheckDate;
GO
指定参数名称
创建过程并声明参数名时，参数名必须以一个 @ 字符开头，并且必须在过程范围内是唯一的。
显式命名参数并将相应的值赋给过程调用中的每个参数允许按任意顺序提供参数。 例如，如果过程 my_proc 应有三个参数，分别命名为 @first、@second 和 @third，则可以将传递到该过程的值赋给参数名称，例如：EXECUTE my_proc @second = 2, @first = 1, @third = 3;。
注意
如果以 @parameter = value 格式提供了一个参数值，则必须按此格式提供所有的后续参数。 如果未以 @parameter = value 格式传递参数值，则必须按 CREATE PROCEDURE 语句中所列的参数顺序（从左到右）提供值。 最好指定参数名称，以便提高可读性和与存储过程的未来版本的兼容性。
警告
任何采用 @parameter = value 格式传入的参数如果拼写错误，就会导致 SQL Server 生成错误，并阻止过程执行。
指定参数数据类型
在 CREATE PROCEDURE 语句中声明时，必须使用数据类型定义参数。 参数的数据类型确定了在调用过程时该参数所接受值的类型和范围。 例如，如果用 tinyint 数据类型定义参数，则在传入该参数时只接受 0 到 255 之间的数值。 如果用与数据类型不兼容的值执行过程，将返回一个错误。
指定参数的默认值
如果在声明参数时指定了默认值，则参数被视为可选的。 在过程调用中不需要为可选参数提供值。
在以下情况下使用参数的默认值：
- 在过程调用中未指定参数值。
- 在过程调用中将 DEFAULT 关键字指定为值。
注意
如果默认值是包含嵌入空格或标点符号的字符串，或者以数字开头（例如，6abc），那么该默认值必须用直的单引号引起来。
注意
默认参数在 Azure Synapse Analytics 或分析平台系统 (PDW) 中不受支持。
如果没有合适的值可以指定为参数的默认值，则指定 NULL 为默认值。 如果在未提供参数值的情况下执行过程，最好让过程返回自定义的消息。
下列示例创建带有一个输入参数 uspGetSalesYTD 的 @SalesPerson过程。 NULL 被指定为该参数的默认值并在错误处理语句中使用，以便在未指定 @SalesPerson 参数值的情况下执行过程时返回自定义错误消息。
USE AdventureWorks2022;
GO
IF OBJECT_ID('Sales.uspGetSalesYTD', 'P') IS NOT NULL
DROP PROCEDURE Sales.uspGetSalesYTD;
GO
CREATE PROCEDURE Sales.uspGetSalesYTD
@SalesPerson nvarchar(50) = NULL -- NULL default value
AS
SET NOCOUNT ON;
-- Validate the @SalesPerson parameter.
IF @SalesPerson IS NULL
BEGIN
PRINT 'ERROR: You must specify the last name of the sales person.'
RETURN
END
-- Get the sales for the specified sales person and
-- assign it to the output parameter.
SELECT SalesYTD
FROM Sales.SalesPerson AS sp
JOIN HumanResources.vEmployee AS e ON e.BusinessEntityID = sp.BusinessEntityID
WHERE LastName = @SalesPerson;
RETURN
GO
下列示例执行过程。 第一个语句执行过程，而未指定输入值。 这将导致过程中的错误处理语句返回自定义错误消息。 第二个语句提供了输入值，所以返回了所需的结果集。
-- Run the procedure without specifying an input value.
EXEC Sales.uspGetSalesYTD;
GO
-- Run the procedure with an input value.
EXEC Sales.uspGetSalesYTD N'Blythe';
GO
虽然可以省略已提供默认值的参数，但只能截断不可为 null 的参数列表。 例如，如果过程有 5 个参数，无需使用 @parameter = value 指定参数名称，可以省略第 4 个和第 5 个参数。 但是，只要包括了第 5 个参数就不能跳过第 4 个参数，除非采用 @parameter = value 格式提供参数。
使用默认值指定多个参数
如果指定参数名称，则可以省略参数。 请考虑以下存储过程，其中包含多个具有 NULL 默认值的可选参数。
USE AdventureWorks2022;
GO
IF OBJECT_ID ( 'Production.uspSearchList', 'P' ) IS NOT NULL
DROP PROCEDURE Production.uspSearchList;
GO
CREATE PROCEDURE Production.uspSearchList
@ListPrice money
, @ProductCategoryID int = NULL -- NULL default value
, @ProductSubcategoryID int = NULL -- NULL default value
, @ProductBusinessEntityID int = NULL -- NULL default value
AS
SET NOCOUNT ON;
SELECT
p.Name, p.Class, p.ListPrice, p.ProductID, pc.Name, psc.Name, v.Name
FROM
Production.Product AS p
INNER JOIN Production.ProductSubCategory AS psc ON p.ProductSubcategoryID = psc.ProductSubcategoryID
INNER JOIN Production.ProductCategory AS pc ON psc.ProductCategoryID = pc.ProductCategoryID
INNER JOIN Purchasing.ProductVendor AS pv ON p.ProductID = pv.ProductID
INNER JOIN Purchasing.Vendor AS v ON pv.BusinessEntityID = v.BusinessEntityID
WHERE (p.ListPrice < @ListPrice)
AND (pc.ProductCategoryID = @ProductCategoryID or @ProductCategoryID IS NULL)
AND (psc.ProductSubcategoryID = @ProductSubcategoryID or @ProductSubcategoryID IS NULL)
AND (pv.BusinessEntityID = @ProductBusinessEntityID or @ProductBusinessEntityID IS NULL);
GO
可以指定或省略具有默认值的参数，如以下示例系列所示，只要每个参数都以格式 @parameter = value 提供参数名称：
--Find all Products with a list price less than 150.00 and in the ProductCategoryID = 4
EXEC Production.uspSearchList @ListPrice = 150, @ProductCategoryID = 4;
--Find all Products with a list price less than 150.00 and in the ProductSubCategoryID = 36
EXEC Production.uspSearchList @ListPrice = 150, @ProductSubCategoryID = 36;
--Find all Products with a list price less than 150.00 and from @ProductBusinessEntityID = 1498
EXEC Production.uspSearchList @ListPrice = 150, @ProductBusinessEntityID = 1498;
--Find all Products with a list price less than 150.00 and in the ProductSubCategoryID = 36 and from @ProductBusinessEntityID = 1498
EXEC Production.uspSearchList @ListPrice = 150, @ProductCategoryID = 4, @ProductBusinessEntityID = 1498;
以下示例是无效的 T-SQL 语法，因为在提供参数名称后，必须以相同的方式提供所有后续参数。 始终建议为所有值提供参数名称，并防止错误和混淆。
EXEC Production.uspSearchList @ListPrice = 150, 4, 1498;
指定参数方向
参数的方向可以为输入（表明将值传递给过程的主体），也可以为输出（表明过程将值返回给调用程序）。 默认为输入参数。
若要指定输出参数，必须在 CREATE PROCEDURE 语句的参数定义中指定 OUTPUT 关键字。 当过程退出时，它向调用程序返回输出参数的当前值。 执行过程时，调用程序也必须使用 OUTPUT 关键字，才能将该参数值保存到可以在调用程序中使用的变量中。
下例创建 Production.usp_GetList 过程，该过程返回价格不超过指定金额的产品的列表。 此示例显示如何使用多个 SELECT 语句和多个 OUTPUT 参数。 使用 OUTPUT 参数，外部过程、批或多个 Transact-SQL 语句可以访问在过程执行期间设置的值。
USE AdventureWorks2022;
GO
IF OBJECT_ID ( 'Production.uspGetList', 'P' ) IS NOT NULL
DROP PROCEDURE Production.uspGetList;
GO
CREATE PROCEDURE Production.uspGetList
@Product varchar(40)
, @MaxPrice money
, @ComparePrice money OUTPUT
, @ListPrice money OUT
AS
SET NOCOUNT ON;
SELECT p.[Name] AS Product, p.ListPrice AS 'List Price'
FROM Production.Product AS p
JOIN Production.ProductSubcategory AS s
ON p.ProductSubcategoryID = s.ProductSubcategoryID
WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice;
-- Populate the output variable @ListPprice.
SET @ListPrice = (SELECT MAX(p.ListPrice)
FROM Production.Product AS p
JOIN Production.ProductSubcategory AS s
ON p.ProductSubcategoryID = s.ProductSubcategoryID
WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice);
-- Populate the output variable @compareprice.
SET @ComparePrice = @MaxPrice;
GO
执行 usp_GetList 以返回价格低于 $700 的 Adventure Works 产品 (Bikes) 的列表。 OUTPUT 参数 @cost 和 @compareprices 与流控制语言一起使用，用以在“消息”窗口中返回消息。
注意
OUTPUT 变量必须在过程创建和变量使用期间进行定义。 参数名称和变量名称不一定要匹配。 但是，数据类型和参数定位必须匹配（除非使用 @listprice = variable）。
DECLARE @ComparePrice money, @Cost money ;
EXECUTE Production.uspGetList '%Bikes%', 700,
@ComparePrice OUT,
@Cost OUTPUT
IF @Cost <= @ComparePrice
BEGIN
PRINT 'These products can be purchased for less than
$'+RTRIM(CAST(@ComparePrice AS varchar(20)))+'.'
END
ELSE
PRINT 'The prices for all products in this category exceed
$'+ RTRIM(CAST(@ComparePrice AS varchar(20)))+'.';
下面是部分结果集：
Product List Price
-------------------------------------------------- ------------------
Road-750 Black, 58 539.99
Mountain-500 Silver, 40 564.99
Mountain-500 Silver, 42 564.99
...
Road-750 Black, 48 539.99
Road-750 Black, 52 539.99
(14 row(s) affected)
These items can be purchased for less than $700.00.