以下示例将创建 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
|
|
执行 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
|
|
下面是部分结果集：
| 复制代码 |
|
|---|---|
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
下面是部分结果集：
| 复制代码 |
|
|---|---|
___FCKpd___2 |
|
+ RTRIM(CAST(@compareprice AS varchar(20)))+'.'
下面是部分结果集：
| 复制代码 |
|
|---|---|
___FCKpd___2 |
|