创建存储过程与OUTPUT参数-优快云博客

以下示例将创建 usp_GetList 存储过程。此过程将返回价格不超过指定数值的产品的列表。此示例显示如何使用多个 SELECT 语句和多个 OUTPUT 参数。OUTPUT 参数允许外部过程、批处理或多条 Transact-SQL 语句在过程执行期间访问设置的某个值。

	复制代码
USE AdventureWorks; GO IF OBJECT_ID ( 'Production.usp_GetList', 'P' ) IS NOT NULL DROP PROCEDURE Production.usp_GetList; GO CREATE PROCEDURE Production.usp_GetList @product varchar(40) , @maxprice money , @compareprice money OUTPUT , @listprice money OUT AS SELECT p.name AS Product, p.ListPrice AS 'List Price' FROM Production.Product p JOIN Production.ProductSubcategory s ON p.ProductSubcategoryID = s.ProductSubcategoryID WHERE s.name LIKE @product AND p.ListPrice < @maxprice; -- Populate the output variable @listprice. SET @listprice = (SELECT MAX(p.ListPrice) FROM Production.Product p JOIN Production.ProductSubcategory s ON p.ProductSubcategoryID = s.ProductSubcategoryID WHERE s.name LIKE @product AND p.ListPrice < @maxprice); -- Populate the output variable @compareprice. SET @compareprice = @maxprice; GO

复制代码

USE AdventureWorks;
GO
IF OBJECT_ID ( 'Production.usp_GetList', 'P' ) IS NOT NULL 
    DROP PROCEDURE Production.usp_GetList;
GO
CREATE PROCEDURE Production.usp_GetList @product varchar(40) 
    , @maxprice money 
    , @compareprice money OUTPUT
    , @listprice money OUT
AS
    SELECT p.name AS Product, p.ListPrice AS 'List Price'
    FROM Production.Product p
    JOIN Production.ProductSubcategory s 
      ON p.ProductSubcategoryID = s.ProductSubcategoryID
    WHERE s.name LIKE @product AND p.ListPrice < @maxprice;
-- Populate the output variable @listprice.
SET @listprice = (SELECT MAX(p.ListPrice)
        FROM Production.Product p
        JOIN  Production.ProductSubcategory 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 产品（自行车）的列表。OUTPUT 参数 @cost 和 @compareprices 用于流控制语言，以便在“消息”窗口中返回消息。

注意：
OUTPUT 变量必须在创建过程时或使用变量时定义。参数名和变量名不一定要匹配；但是，除非使用 @listprice = variable 的形式，否则数据类型和参数位置必须匹配。

	复制代码
DECLARE @compareprice money, @cost money EXECUTE Production.usp_GetList '%Bikes%', 700, @compareprice OUT, @cost OUTPUT IF @cost <= @compareprice BEGIN PRINT 'These products can be purchased for less than

复制代码

DECLARE @compareprice money, @cost money 
EXECUTE Production.usp_GetList '%Bikes%', 700, 
    @compareprice OUT, 
    @cost OUTPUT
IF @cost <= @compareprice 
BEGIN
    PRINT 'These products can be purchased for less than

下面是部分结果集：

	复制代码
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.

复制代码

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.

+RTRIM(CAST(@compareprice AS varchar(20)))+'.' END ELSE PRINT 'The prices for all products in this category exceed

下面是部分结果集：