create proc testA
as
select id,name from sysobjects
go
create proc testB
as
create table #tmp(a int,b varchar(100))
insert #tmp exec testA
select * from #tmp
go
exec testB
as
select id,name from sysobjects
go
create proc testB
as
create table #tmp(a int,b varchar(100))
insert #tmp exec testA
select * from #tmp
go
exec testB
as
select top 10 id,name from sysobjects
go
create proc testB
as
create table #tmp(a int,b varchar(100))
insert #tmp exec testA
select * from #tmp
go
exec testB
--drop proc testA,testB(所影响的行数为 10 行)a b
----------- ----------------------------------------------------------------------------------------------------
1 sysobjects
2 sysindexes
3 syscolumns
4 systypes
6 syscomments
8 sysfiles1
9 syspermissions
10 sysusers
11 sysproperties
12 sysdepends(所影响的行数为 10 行)
as
select * from 表
gocreate proc B
as
create table result
(
...
)insert result exec Aselect * from result
go
1。
使用 OUTPUT 游标参数
OUTPUT 游标参数用来将存储过程的局部游标传递回调用批处理、存储过程或触发器。首先,创建以下过程,在 titles 表上声明并打开一个游标:USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'titles_cursor' and type = 'P')
DROP PROCEDURE titles_cursor
GO
CREATE PROCEDURE titles_cursor @titles_cursor CURSOR VARYING OUTPUT
AS
SET @titles_cursor = CURSOR
FORWARD_ONLY STATIC FOR
SELECT *
FROM titlesOPEN @titles_cursor
GO接下来,执行一个批处理,声明一个局部游标变量,执行上述过程以将游标赋值给局部变量,然后从该游标提取行。USE pubs
GO
DECLARE @MyCursor CURSOR
EXEC titles_cursor @titles_cursor = @MyCursor OUTPUT
WHILE (@@FETCH_STATUS = 0)
BEGIN
FETCH NEXT FROM @MyCursor
END
CLOSE @MyCursor
DEALLOCATE @MyCursor
GO
create proc a
as
select top 10 name from sysobjects
go--存储过程b,调用存储过程a,并返回记录集
create proc b
as
create table #t(name sysname)
insert into #t exec a
select * from #t
go--调用测试
exec bgo
--删除测试
drop proc a,b/*--测试结果
name
-------------
a
b
p_exporttb
SctTable
syscolumns
syscomments
sysconstraints
sysdepends
sysfilegroups
sysfiles(所影响的行数为 10 行)
--*/