请教高手关于在存储过程里用了2个exec后在返回记录集的时候取值失败！

Question

第一个是EXEC是执行的将1组数据插入临时表
set @sqla='insert into #temp99 select a.out_no, a.line,a.out_date FROM out_stock a'
exec @sqla
第二个是EXEC是显示内容
set @sqlb= select * FROM out_stock a,#temp99'
exec @sqlb
在不执行第一个exec的情况下程序运行正常，加了第一个后在取记录集的时候就出错
请教高手帮忙解决一下（目的是既要执行第一个exec友要第二个exec的记录集）

Answer 1

select top 0 a.out_no, a.line,a.out_date into #temp99 FROM out_stock a
set @sqla='insert into #temp99 select a.out_no, a.line,a.out_date FROM out_stock a'
exec @sqla
set @sqlb= 'select * FROM out_stock a,#temp99'
exec @sqlb

Answer 2

没有明白你的意思，实际中是要向#temp99这个表添加记录的

Answer 3

有没有办法把第一个EXEC的记录集清除

Answer 4

你要表达什么意思？
你的sql语句，首先向 #temp99添加了记录，然后又读取#temp99的记录。第二次执行的时候，#temp99已经添加了，你想除去添加的记录还是怎么着？

Answer 5

是这样的
set @sqla='insert into #temp99 select a.out_no, a.line,a.out_date FROM out_stock a'
exec @sqla  --执行这个的目的是把out_stock的数据加到临时表#temp99里加个顺序号
是想清除由这个语句产生的记录集，不是清除临时#temp99表里的数据
set @sqlb= 'select * FROM out_stock a,#temp99'
exec @sqlb  

Answer 6

补充
 set @sqla='insert into #temp99 select a.out_no, a.line,a.out_date FROM out_stock a'
 exec @sqla  --执行这个的目的是把out_stock的数据加到临时表#temp99里加个顺序号
 是想清除由这个语句产生的记录集，不是清除临时#temp99表里的数据
 set @sqlb= 'select * FROM out_stock a,#temp99'
 exec @sqlb   
最后这个exec执行的数据记录集是要用的

Answer 7

--就是这个意思呗，结果没错
if object_id('Tempdb..#a') is not null drop table #a
if object_id('Tempdb..#b') is not null drop table #bcreate table #a(
[Id] int null,
[Title] nvarchar(100) null
)create table #b(
[Id] int null,
[Memo] nvarchar(100) null
)
 
Insert Into #a
select 1,'a' union all
select 2,'b' union all
select 3,'c' union all
select 4,'d' union all
select 5,'e'  select * from #a
declare @sqla nvarchar(100)
declare @sqlb nvarchar(100)
set @sqla='Insert Into #b(Id) select Id from #a '
exec(@sqla)set @sqlb='select * from #a,#b'
exec(@sqlb)-----------------------(5 行受影响)
Id          Title
----------- ----------------------------------------------------------------------------------------------------
1           a
2           b
3           c
4           d
5           e(5 行受影响)(5 行受影响)Id          Title                                                                                                Id          Memo
----------- ---------------------------------------------------------------------------------------------------- ----------- ----------------------------------------------------------------------------------------------------
1           a                                                                                                    1           NULL
2           b                                                                                                    1           NULL
3           c                                                                                                    1           NULL
4           d                                                                                                    1           NULL
5           e                                                                                                    1           NULL
1           a                                                                                                    2           NULL
2           b                                                                                                    2           NULL
3           c                                                                                                    2           NULL
4           d                                                                                                    2           NULL
5           e                                                                                                    2           NULL
1           a                                                                                                    3           NULL
2           b                                                                                                    3           NULL
3           c                                                                                                    3           NULL
4           d                                                                                                    3           NULL
5           e                                                                                                    3           NULL
1           a                                                                                                    4           NULL
2           b                                                                                                    4           NULL
3           c                                                                                                    4           NULL
4           d                                                                                                    4           NULL
5           e                                                                                                    4           NULL
1           a                                                                                                    5           NULL
2           b                                                                                                    5           NULL
3           c                                                                                                    5           NULL
4           d                                                                                                    5           NULL
5           e                                                                                                    5           NULL(25 行受影响)