指定存储过程中的参数

项目
05/25/2023

适用于：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需要名为、 @second和 @third的三个@first参数，则可以将传递给过程的值分配给参数名称，例如：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 或 Analytics Platform System (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 的参数列表只能截断。例如，如果一个过程有五个参数，如果不使用指定参数名称，则可以省略第四个和第五个 @parameter = value参数。但是，只要包含第五个参数，就不能跳过第四个参数，除非参数以的形式 @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.