通过指定过程参数，调用程序可以将值传递给过程的主体。

如果将参数标记为 OUTPUT 参数，则过程参数还可以将值返回给调用程序。

一个过程最多可以有 2100 个参数，每个参数都有名称、数据类型和方向。 还可以为参数指定默认值（可选）。

将值传递给参数

 使用过程调用提供的参数值必须为常量或变量，不能将函数名称作为参数值。 变量可以是用户定义的变量或系统变量（如 @@spid）。

下列示例演示如何将参数值传递给过程 uspGetWhereUsedProductID。 它们说明了如何将参数作为常量和变量进行传递，以及如何使用变量传递函数值。

USE AdventureWorks2012;  
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();  
GO  
-- Passing the function value as a variable.  
DECLARE @CheckDate datetime;  
SET @CheckDate = GETDATE();  
EXEC dbo.uspGetWhereUsedProductID 819, @CheckDate;  
GO

在存储过程中定义参数

1.需要指定参数名称

2.指定参数数据类型

3.可以指定参数默认值

4.可以指定参数方式(默认为输入参数)

示例1：

USE AdventureWorks2012;  
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

 

 

 

示例2：

USE AdventureWorks2012;  
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

运行如下脚本

DECLARE @ComparePrice money, @Cost money ;  
EXECUTE Production.uspGetList \'%Bikes%\', 700,   
    @ComparePrice OUT,   
    @Cost OUTPUT  
print \'Cost\'+RTRIM(CAST(@Cost AS varchar(20)))
print \'ComparePrice\'+RTRIM(CAST(@ComparePrice AS varchar(20)))
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)))+\'.\';

效果

 

 

 

版权声明：本文为Vincent-yuan原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://www.cnblogs.com/Vincent-yuan/p/12616526.html