create procedure sp2 @a int as set @a=@a-1 exec sp1 @a
调用 exec sp1 10
输出 1 2 3 4 5 6 7 8 9 10
--sql 2000使用 OUTPUT 参数 OUTPUT 参数允许外部过程、批处理或多条 Transact-SQL 语句访问在过程执行期间设置的某个值。下面的示例创建一个存储过程 (titles_sum),并使用一个可选的输入参数和一个输出参数。首先,创建过程:USE pubs GO IF EXISTS(SELECT name FROM sysobjects WHERE name = 'titles_sum' AND type = 'P') DROP PROCEDURE titles_sum GO USE pubs GO CREATE PROCEDURE titles_sum @@TITLE varchar(40) = '%', @@SUM money OUTPUT AS SELECT 'Title Name' = title FROM titles WHERE title LIKE @@TITLE SELECT @@SUM = SUM(price) FROM titles WHERE title LIKE @@TITLE GO接下来,将该 OUTPUT 参数用于控制流语言。 说明 OUTPUT 变量必须在创建表和使用该变量时都进行定义。 参数名和变量名不一定要匹配,不过数据类型和参数位置必须匹配(除非使用 @@SUM = variable 形式)。 DECLARE @@TOTALCOST money EXECUTE titles_sum 'The%', @@TOTALCOST OUTPUT IF @@TOTALCOST < 200 BEGIN PRINT ' ' PRINT 'All of these titles can be purchased for less than $200.' END ELSE SELECT 'The total cost of these titles is $' + RTRIM(CAST(@@TOTALCOST AS varchar(20)))下面是结果集:Title Name ------------------------------------------------------------------------ The Busy Executive's Database Guide The Gourmet Microwave The Psychology of Computer Cooking(3 row(s) affected)Warning, null value eliminated from aggregate.
All of these titles can be purchased for less than $200.
--sql 2005使用 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 执行 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 $'+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.
--这个适合你.怎么样在存储过程B里面调用存储过程A执行后的结果集请问大家这样一个问题,有存储过程A和存储过程B,两个存储过程执行时都要传递参数,问题: 怎么样在存储过程B里面调用存储过程A执行后的结果集?就是在存储过程B里面再对存储过程A的结果集进行查询. (能举个例子列好) 谢谢了!--第一 create table a ( fid numeric(10), fno varchar(10), fname varchar(50) )insert into a values(1,'1','1')--第二步 IF EXISTS (SELECT name FROM sysobjects WHERE name = 'p_c' AND type = 'P') DROP PROCEDURE p_c GO CREATE PROCEDURE p_c AS SELECT * from a GO EXECUTE p_c GO--第三 IF EXISTS (SELECT name FROM sysobjects WHERE name = 'p_b' AND type = 'P') DROP PROCEDURE p_b GO CREATE PROCEDURE p_b AS IF EXISTS(SELECT name FROM sysobjects WHERE name = N'b' AND type = 'U') DROP TABLE b create table b ( fid numeric(10), fno varchar(10), fname varchar(50) ) insert into b exec p_c select * from b GO EXECUTE p_b GO
create procedure sp1
@a int
as
if @a>1
exec sp2 @a
print @a
create procedure sp2
@a int
as
set @a=@a-1
exec sp1 @a
调用
exec sp1 10
输出
1
2
3
4
5
6
7
8
9
10
OUTPUT 参数允许外部过程、批处理或多条 Transact-SQL 语句访问在过程执行期间设置的某个值。下面的示例创建一个存储过程 (titles_sum),并使用一个可选的输入参数和一个输出参数。首先,创建过程:USE pubs
GO
IF EXISTS(SELECT name FROM sysobjects
WHERE name = 'titles_sum' AND type = 'P')
DROP PROCEDURE titles_sum
GO
USE pubs
GO
CREATE PROCEDURE titles_sum @@TITLE varchar(40) = '%', @@SUM money OUTPUT
AS
SELECT 'Title Name' = title
FROM titles
WHERE title LIKE @@TITLE
SELECT @@SUM = SUM(price)
FROM titles
WHERE title LIKE @@TITLE
GO接下来,将该 OUTPUT 参数用于控制流语言。 说明 OUTPUT 变量必须在创建表和使用该变量时都进行定义。
参数名和变量名不一定要匹配,不过数据类型和参数位置必须匹配(除非使用 @@SUM = variable 形式)。 DECLARE @@TOTALCOST money
EXECUTE titles_sum 'The%', @@TOTALCOST OUTPUT
IF @@TOTALCOST < 200
BEGIN
PRINT ' '
PRINT 'All of these titles can be purchased for less than $200.'
END
ELSE
SELECT 'The total cost of these titles is $'
+ RTRIM(CAST(@@TOTALCOST AS varchar(20)))下面是结果集:Title Name
------------------------------------------------------------------------
The Busy Executive's Database Guide
The Gourmet Microwave
The Psychology of Computer Cooking(3 row(s) affected)Warning, null value eliminated from aggregate.
All of these titles can be purchased for less than $200.
以下示例将创建 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
$'+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.
怎么样在存储过程B里面调用存储过程A执行后的结果集?就是在存储过程B里面再对存储过程A的结果集进行查询. (能举个例子列好)
谢谢了!--第一
create table a
( fid numeric(10),
fno varchar(10),
fname varchar(50)
)insert into a
values(1,'1','1')--第二步
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'p_c' AND type = 'P')
DROP PROCEDURE p_c
GO
CREATE PROCEDURE p_c
AS
SELECT * from a
GO
EXECUTE p_c
GO--第三
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'p_b' AND type = 'P')
DROP PROCEDURE p_b
GO
CREATE PROCEDURE p_b
AS
IF EXISTS(SELECT name FROM sysobjects WHERE name = N'b' AND type = 'U')
DROP TABLE b
create table b
(
fid numeric(10),
fno varchar(10),
fname varchar(50)
)
insert into b exec p_c
select * from b
GO
EXECUTE p_b
GO
as
exec A
exce procedureb 'aa' 'bb''aa' 'bb' 是procedureb存储过程的参数