34,593
社区成员
发帖
与我相关
我的任务
分享
比如存储过程a调用存储过程b
存储a里面有:
exec proc b --执行b需要返回数据集(临时表)在存储a里面使用
最后存储a也要返回数据集
最后执行存储a的时候返回的数据就有2个数据集了,我只想要存储a返回的数据集,不要exec proc b的数据集。
--调用下列存储过程生成临时表##tmp_saveFloatRainfallPlus
--用于和临时表##tmp_saveFloatRainfallTime合并成一个表
exec dbo.[proc_getFloatRainfallPlus]
declare @strsql nvarchar(4000)
declare @curTime datetime
set @curTime=GETDATE()
--set @curTime='2011-09-18 7:43:09'
declare @timestampHH int --时间戳
select @timestampHH=DATEPART(HH,@curTime)
--debug
--exec dbo.proc_getFloatRainfall @stcd,'today',@todayRain output,'0.0'
--exec dbo.proc_getFloatRainfall @stcd,'yestoday','0.0',@yestodayRain output
set @strsql=''
if(@timestampHH>=8)
begin
declare @lastest24Hour_big8_f nvarchar(500)
declare @lastest24Hour_big8_b nvarchar(500)
declare @starti int
declare @endi int
set @starti=8
set @endi=7
while @starti<=23
begin
set @lastest24Hour_big8_f=ISNULL(@lastest24Hour_big8_f+',','')
+'ISNULL(p'+convert(varchar(2),@starti)+',0) p'+CONVERT(varchar(2),@starti)
set @starti=@starti+1
end
set @lastest24Hour_big8_f=@lastest24Hour_big8_f+',p0=0,p1=0,p2=0,p3=0,p4=0,p5=0,p6=0,p7=0'
set @strsql='select STCD,'+@lastest24Hour_big8_f+' from st_rain_s where convert(varchar(10),TM,120)='''+convert(varchar(10),@curTime,120)+''''
insert into ##tmp_saveFloatRainfallTime
exec sp_executesql @strsql
set @strsql=''
while @endi>=0
begin
set @lastest24Hour_big8_b=ISNULL(@lastest24Hour_big8_b+',','')
+'ISNULL(p'+convert(varchar(2),@endi)+',0) p'+CONVERT(varchar(2),@endi)
set @endi=@endi-1
end
set @lastest24Hour_big8_b=@lastest24Hour_big8_b+',p8=0,p9=0,p10=0,p11=0,p12=0,p13=0,p14=0,p15=0,p16=0,p17=0,p18=0,p19=0,p20=0,p21=0,p22=0,p23=0'
set @strsql='select STCD,'+@lastest24Hour_big8_b+' from st_rain_s where convert(varchar(10),TM,120)='''+convert(varchar(10),dateadd(day,1,@curTime),120)+''''
insert into ##tmp_saveFloatRainfallTime
exec sp_executesql @strsql
--debug
--print @strSql
end
else
begin
declare @lastest24Hour_less8_f nvarchar(500)
declare @lastest24Hour_less8_b nvarchar(500)
declare @starti1 int
declare @endi1 int
set @starti1=8
set @endi1=0
while @starti1<=23
begin
set @lastest24Hour_less8_f=ISNULL(@lastest24Hour_less8_f+',','')
+'ISNULL(p'+convert(varchar(2),@starti1)+',0) p'+CONVERT(varchar(2),@starti1)
set @starti1=@starti1+1
end
set @lastest24Hour_less8_f=@lastest24Hour_less8_f+',p0=0,p1=0,p2=0,p3=0,p4=0,p5=0,p6=0,p7=0'
set @strsql='select STCD,'+@lastest24Hour_less8_f+' from st_rain_s where convert(varchar(10),TM,120)='''+CONVERT(varchar(10),dateadd(day,-1,@curTime),120)+''''
insert into ##tmp_saveFloatRainfallTime
exec sp_executesql @strsql
set @strsql=''
while @endi1<=7
begin
set @lastest24Hour_less8_b=ISNULL(@lastest24Hour_less8_b+',','')
+'ISNULL(p'+convert(varchar(2),@endi1)+',0) p'+CONVERT(varchar(2),@endi1)
set @endi1=@endi1+1
end
set @lastest24Hour_less8_b='p8=0,p9=0,p10=0,p11=0,p12=0,p13=0,p14=0,p15=0,p16=0,p17=0,p18=0,p19=0,p20=0,p21=0,p22=0,p23=0,'+@lastest24Hour_less8_b
set @strsql='select STCD,'+@lastest24Hour_less8_b+' from st_rain_s where convert(varchar(10),TM,120)='''+convert(varchar(10),@curTime,120)+''''
insert into ##tmp_saveFloatRainfallTime
exec sp_executesql @strsql
--debug
--print @strSql
end
--临时表数据集合并
IF object_id('[tempdb]..##tmp_saveFloatRainfallPlus') IS NOT NULL
select t.STCD
,a.stName
,(select [name] from adcd_code where adcd=(select adcd from stcd_info where stcd=t.STCD)) as area
,t.todayRain,t.yestodayRain, ISNULL(o.p8,0) p8,ISNULL(o.p9,0) p9,ISNULL(o.p10,0) p10,ISNULL(o.p11,0) p11,ISNULL(o.p12,0) p12,ISNULL(o.p13,0) p13,ISNULL(o.p14,0) p14,ISNULL(o.p15,0) p15,ISNULL(o.p16,0) p16,ISNULL(o.p17,0) p17,ISNULL(o.p18,0) p18,ISNULL(o.p19,0) p19,ISNULL(o.p20,0) p20,ISNULL(o.p21,0) p21,ISNULL(o.p22,0) p22,ISNULL(o.p23,0) p23,ISNULL(o.p0,0) p0,ISNULL(o.p1,0) p1,ISNULL(o.p2,0) p2,ISNULL(o.p3,0) p3,ISNULL(o.p4,0) p4,ISNULL(o.p5,0) p5,ISNULL(o.p6,0) p6,ISNULL(o.p7,0) p7
from (select STCD,SUM(todayRain) todayRain,SUM(yestodayRain) yestodayRain from ##tmp_saveFloatRainfallPlus group by STCD) t
left join
(select STCD,SUM(p8) p8,SUM(p9) p9,SUM(p10) p10,SUM(p11) p11,SUM(p12) p12,SUM(p13) p13,SUM(p14) p14,SUM(p15) p15,SUM(p16) p16,SUM(p17) p17,SUM(p18) p18,SUM(p19) p19,SUM(p20) p20,SUM(p21) p21,SUM(p22) p22,SUM(p23) p23,SUM(p0) p0,SUM(p1) p1,SUM(p2) p2,SUM(p3) p3,SUM(p4) p4,SUM(p5) p5,SUM(p6) p6,SUM(p7) p7 from ##tmp_saveFloatRainfallTime group by STCD) o on o.STCD=t.STCD
left join stcd_info a
on t.STCD=a.stcd
end
/*
存储过程最后我是使用了select 返回数据集,
问题是出在第3行: exec dbo.[proc_getFloatRainfallPlus] (返回的是##临时表),这里主要调用其他存储过程返回数据用于在当前存储过程使用,如果不返还##临时表,在当前存储过程创建#临时表,这样做会出现insert exec嵌套异常,怎么解决了?
*/
详细介绍下:
存储过程 proc b
/*
里面有个全局临时表##b,该表主要在存储过程a里面作为临时被使用
*/
存储过程 proc a
/*
首先的执行存储过程b,所以有 exec proc b操作,这个操作后##b表肯定是有数据的,
存储过程a最后面有select ... 返回数据,但是最后面执行 exec proc a的时候返回的数据是2个数据集:
1、exec proc b
2、select ...
我只要第二条的数据集,不要1的。
*/