create proc a as create table #t(....) insert into #t exec b
但是exec b之后,返回的列要固定,如果不定,可能要用其他方法。
create table test(id int identity(1,1),code varchar(8)) insert into test select 'aaaa' union select 'bbbb' gocreate procedure sp_test2 @id int output, @code varchar(8) output as begin select @id=id,@code=code from test where code='aaaa' return end gocreate procedure sp_test1 as begin declare @id int,@code varchar(8) exec sp_test2 @id out,@code out --注意这里 select @id as nid,@code as ncode end goexec sp_test1 go /* nid ncode ----------- -------- 1 aaaa */drop procedure sp_test1,sp_test2 drop table test
as
create table #t(....)
insert into #t
exec b
insert into test select 'aaaa' union select 'bbbb'
gocreate procedure sp_test2
@id int output,
@code varchar(8) output
as
begin
select @id=id,@code=code from test where code='aaaa'
return
end
gocreate procedure sp_test1
as
begin
declare @id int,@code varchar(8)
exec sp_test2 @id out,@code out --注意这里
select @id as nid,@code as ncode
end
goexec sp_test1
go
/*
nid ncode
----------- --------
1 aaaa
*/drop procedure sp_test1,sp_test2
drop table test
如果你只是要返回数据应该用function
http://msdn.microsoft.com/en-us/library/ms191165(v=sql.105).aspx
if object_id('tmp') is not null
drop table tmp
select * into tmp from table第二个sp从tmp表取数操作。
insert into #TB(XXX)
exec ProcName
as
insert into #t select 1create proc a
as
create table #t(id int)
exec b
select * from #t