如何用类似循环的方法合并11个帐套的合并TB表的存储过程.

bonesoup 2008-10-08 03:39:28
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语句,再执行它.
或者还有更好的方法.谢谢先.


...全文
109 4 打赏 收藏 转发到动态 举报
写回复
用AI写文章
4 条回复
切换为时间正序
请发表友善的回复…
发表回复
bonesoup 2008-10-08
  • 打赏
  • 举报
回复
如果实在不行只能这样笨笨地联合了.可是这样相同的代码就重复了11次.郁闷.
create proc bsT_TB
as
declare @vp varchar(8000)
set @vp='select * from tbl帐套1 where db=''帐套1'' union
select * from tbl帐套2 where db=''帐套2'' union
.....
select * from tbl帐套11 where db='帐套11'''

exec(@vp)

bonesoup 2008-10-08
  • 打赏
  • 举报
回复
我简化一下我的提问.
以下存储过程是按参数(帐套)生成表.
现有11个帐套,如何用简洁的语句,实现11个表的联合.

create proc bsT_TB
@pat3 varchar(5)
--@pat3=帐套
as
declare @vp varchar(8000)
set @vp='select * from tbl'+ @pat3+ ' where db='+@pat3
exec(@vp)
bonesoup 2008-10-08
  • 打赏
  • 举报
回复
我想想,该怎么描述更清楚呢.
1个存储过程,1个参数(帐套),生成1张表.
现在有11个帐套,欲联合这11张表为1张表.
pt1314917 2008-10-08
  • 打赏
  • 举报
回复
没读懂什么意思。

27,579

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧