指定存储过程中的参数

2025-08-15

适用于：SQL Server Azure SQL 数据库 Azure SQL 托管实例 Azure Synapse Analytics 分析平台系统（PDW）Microsoft Fabric 预览版中的 SQL 数据库

通过指定过程参数，调用程序可以将值传递给过程的主体。这些值可用于过程执行期间的各种目的。如果参数被标记为 OUTPUT 参数，则过程参数还可以将值返回到调用程序。

一个过程最多可以有 2,100 个参数：每个分配的名称、数据类型和方向。还可以为参数指定默认值（可选）。

下面的章节提供有关将值传递给参数以及在过程调用期间如何使用每个参数属性的信息。

Note

请参阅本文练习的 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;。

Note

如果以 @parameter = value 格式提供了一个参数值，则必须按此格式提供所有的后续参数。如果未在窗体 @parameter = value中传递参数值，则必须按相同的顺序（从左到右）提供值，因为参数列在语句中 CREATE PROCEDURE 。最好指定参数名称，以便提高可读性和与存储过程的未来版本的兼容性。

Warning

任何采用 @parameter = value 格式传入的参数如果拼写错误，就会导致 SQL Server 生成错误，并阻止过程执行。

指定参数数据类型

在语句中 CREATE PROCEDURE 声明参数时，必须使用数据类型定义参数。参数的数据类型确定了在调用过程时该参数所接受值的类型和范围。例如，如果用 tinyint 数据类型定义参数，则在传入该参数时只接受 0 到 255 之间的数值。如果用与数据类型不兼容的值执行过程，将返回一个错误。

指定参数默认值

如果在声明参数时指定了默认值，则参数被视为可选的。在过程调用中不需要为可选参数提供值。

在以下情况下使用参数的默认值：

在过程调用中未指定参数值。
关键字 DEFAULT 指定为过程调用中的值。

Note

如果默认值是包含嵌入空格或标点符号的字符串，或者以数字开头（例如，6abc），那么该默认值必须用直的单引号引起来。

Note

默认参数在 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;

指定参数方向

参数的方向可以为输入（表明将值传递给过程的主体），也可以为输出（表明过程将值返回给调用程序）。默认为输入参数。

若要指定输出参数， OUTPUT 必须在语句中参数的定义中 CREATE PROCEDURE 指定关键字。当过程退出时，它向调用程序返回输出参数的当前值。调用程序在执行过程时还必须使用 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) 的列表。 @cost参数OUTPUT并@compareprices用于流控制语言，以在“消息”窗口中返回消息。

Note

必须在 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.

反馈

此页面是否有帮助？

通过

指定存储过程中的参数

将值传递到参数中

指定参数名称

指定参数数据类型

指定参数默认值

使用默认值指定多个参数

指定参数方向

相关内容

反馈

其他资源