如何用类似循环的方法合并11个帐套的合并TB表的存储过程.
create proc bsT_TB
@pat1 varchar(7),@pat2 varchar(7),@pat3 varchar(5)
--@pat1=期初,@pat2=期末,@pat3=帐套
as
--年初
declare @vp1 varchar(7)
set @vp1=left(@pat1,4)+'001'
set nocount on
declare @vp varchar(8000)
set @vp='select T1.ACCNT_CODE ,T2.ACCNT_NAME,bYear,
(case when (T1.ACCNT_CODE between 400000000 and 600000000) then (YearD+YearC)-(MountD+MountC) else bMonth end) as bMonth,
MountD,MountC,YearD,YearC,(bYear+YearD+YearC) as eMouth
from (select ACCNT_CODE,
SUM(CASE WHEN (PERIOD<'+@vp1+' and ACCNT_CODE not between 400000000 and 600000000) THEN AMOUNT ELSE 0 END) AS ''bYear'',
SUM(CASE WHEN (PERIOD<'+@pat1+' and ACCNT_CODE not between 400000000 and 600000000) THEN AMOUNT ELSE 0 END) AS ''bMonth'',
SUM(CASE WHEN (PERIOD between '+@pat1+' and '+@pat2+' and D_C=''D'') THEN AMOUNT ELSE 0 END) AS ''MountD'',
SUM(CASE WHEN (PERIOD between '+@pat1+' and '+@pat2+' and D_C=''C'') THEN AMOUNT ELSE 0 END) AS ''MountC'',
SUM(CASE WHEN (PERIOD between '+@vp1+' and '+@pat2+' and D_C=''D'') THEN AMOUNT ELSE 0 END) AS ''YearD'',
SUM(CASE WHEN (PERIOD between '+@vp1+' and '+@pat2+' and D_C=''C'') THEN AMOUNT ELSE 0 END) AS ''YearC''
from SALFLDG'+@pat3+' group by ACCNT_CODE) T1,SSRFACC T2
where T1.ACCNT_CODE=T2.ACCNT_CODE
and T2.SUN_DB='''+@pat3+'''
order BY T1.ACCNT_CODE'
--print @vp
exec(@vp)
set nocount off
以上是一个帐套的TB表生成的存储过程.
现在想做11个帐套的合并TB表(这11个帐套名,可以用一句sql语句取得),即重复11次不同帐套的exec(@vp),然后将它们union.再group by
该如何实现呢?类似VB中for循环,循环利用上面的语句,联接出一句sql语句,再执行它.
或者还有更好的方法.谢谢先.