34,593
社区成员
发帖
与我相关
我的任务
分享
create proc B
as
exec A
怎么样在存储过程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
使用 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.
使用 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.
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