create proc up_getbzsfhz_csx (@xmkeys varchar(1000), @yxkeys varchar(1000), @fylx varchar(5)) as exec('SELECT sfyxzd.sfyxzd_yxkey, sfyxzd.sfyxzd_yxmc, ffyxmzd.sffyxmzd_xmmc, count(sfsfyw.sfsfyw_xskey) as count1, sum(sfsfywxm.sfsfywxm_je) as je FROM sfsfyw, sfsfywxm, sffyxmzd, sfxszd, sfyxzd WHERE ( sfsfyw.sfsfyw_sfkey = sfsfywxm.sfsfywxm_sfkey ) AND ( sfsfyw.sfsfyw_xskey = sfxszd.sfxszd_xskey ) AND ( sfyxzd.sfyxzd_yxkey = sfxszd.sfxszd_yxkey ) AND ( sfsfywxm.sfsfywxm_xmkey = sffyxmzd.sffyxmzd_xmkey ) and (sfyxzd_yxkey in ('+@yxkeys+') ) and ( sfsfywxm_xmkey in ('+@xmkeys+' ) ) GROUP BY sfyxzd.sfyxzd_yxkey, sfyxzd.sfyxzd_yxmc, sffyxmzd.sffyxmzd_xmmc ')go--调用:exec up_getbzsfhz_csx '1,2,3,4,5,6,7,8','1,3,5,9,10,11,12,6',45
create proc up_getbzsfhz_csx (@xmkeys varchar(100), @yxkeys varchar(100), @fylx char(5)) as SELECT sfyxzd.sfyxzd_yxkey, sfyxzd.sfyxzd_yxmc, ffyxmzd.sffyxmzd_xmmc, count(sfsfyw.sfsfyw_xskey) as count1, sum(sfsfywxm.sfsfywxm_je) as je FROM sfsfyw, sfsfywxm, sffyxmzd, sfxszd, sfyxzd WHERE ( sfsfyw.sfsfyw_sfkey = sfsfywxm.sfsfywxm_sfkey ) AND ( sfsfyw.sfsfyw_xskey = sfxszd.sfxszd_xskey ) AND ( sfyxzd.sfyxzd_yxkey = sfxszd.sfxszd_yxkey ) AND ( sfsfywxm.sfsfywxm_xmkey = sffyxmzd.sffyxmzd_xmkey ) and charindex(','+ltrim(rtrim(cast(sfyxzd_yxkey as char)))+',',','+@yxkeys+',')>0 and charindex(','+ltrim(rtrim(cast(sfsfywxm_xmkey as char)))+',',','+@xmkeys+',')>0 GROUP BY sfyxzd.sfyxzd_yxkey, sfyxzd.sfyxzd_yxmc, sffyxmzd.sffyxmzd_xmmc
create proc up_getbzsfhz_csx (@xmkeys varchar(1000), @yxkeys varchar(1000), @fylx varchar(5)) as exec('SELECT sfyxzd.sfyxzd_yxkey, sfyxzd.sfyxzd_yxmc, ffyxmzd.sffyxmzd_xmmc, count(sfsfyw.sfsfyw_xskey) as count1, sum(sfsfywxm.sfsfywxm_je) as je FROM sfsfyw, sfsfywxm, sffyxmzd, sfxszd, sfyxzd WHERE ( sfsfyw.sfsfyw_sfkey = sfsfywxm.sfsfywxm_sfkey ) AND ( sfsfyw.sfsfyw_xskey = sfxszd.sfxszd_xskey ) AND ( sfyxzd.sfyxzd_yxkey = sfxszd.sfxszd_yxkey ) AND ( sfsfywxm.sfsfywxm_xmkey = sffyxmzd.sffyxmzd_xmkey ) and (sfyxzd_yxkey in ('+@yxkeys+') ) and ( sfsfywxm_xmkey in ('+@xmkeys+' ) ) GROUP BY sfyxzd.sfyxzd_yxkey, sfyxzd.sfyxzd_yxmc, sffyxmzd.sffyxmzd_xmmc ')go--调用:exec up_getbzsfhz_csx '1,2,3,4,5,6,7,8','1,3,5,9,10,11,12,6',45
create proc up_getbzsfhz_csx (@xmkeys varchar(1000), @yxkeys varchar(1000), @fylx varchar(5)) as SELECT sfyxzd.sfyxzd_yxkey, sfyxzd.sfyxzd_yxmc, ffyxmzd.sffyxmzd_xmmc, count(sfsfyw.sfsfyw_xskey) as count1, sum(sfsfywxm.sfsfywxm_je) as je FROM sfsfyw, sfsfywxm, sffyxmzd, sfxszd, sfyxzd WHERE ( sfsfyw.sfsfyw_sfkey = sfsfywxm.sfsfywxm_sfkey ) AND ( sfsfyw.sfsfyw_xskey = sfxszd.sfxszd_xskey ) AND ( sfyxzd.sfyxzd_yxkey = sfxszd.sfxszd_yxkey ) AND ( sfsfywxm.sfsfywxm_xmkey = sffyxmzd.sffyxmzd_xmkey ) and @yxkeys like '%,'+cast(sfyxzd_yxkey as varchar(10))+',%' and @xmkeys like '%,'+cast(sfsfywxm_xmkey as varchar(10))+',%' GROUP BY sfyxzd.sfyxzd_yxkey, sfyxzd.sfyxzd_yxmc, sffyxmzd.sffyxmzd_xmmc go--调用:exec up_getbzsfhz_csx '1,2,3,4,5,6,7,8','1,3,5,9,10,11,12,6',45
create proc up_getbzsfhz_csx (@xmkeys varchar(1000), @yxkeys varchar(1000), @fylx varchar(5)) as SELECT sfyxzd.sfyxzd_yxkey, sfyxzd.sfyxzd_yxmc, ffyxmzd.sffyxmzd_xmmc, count(sfsfyw.sfsfyw_xskey) as count1, sum(sfsfywxm.sfsfywxm_je) as je FROM sfsfyw, sfsfywxm, sffyxmzd, sfxszd, sfyxzd WHERE ( sfsfyw.sfsfyw_sfkey = sfsfywxm.sfsfywxm_sfkey ) AND ( sfsfyw.sfsfyw_xskey = sfxszd.sfxszd_xskey ) AND ( sfyxzd.sfyxzd_yxkey = sfxszd.sfxszd_yxkey ) AND ( sfsfywxm.sfsfywxm_xmkey = sffyxmzd.sffyxmzd_xmkey ) and @yxkeys like '%,'+cast(sfyxzd_yxkey as varchar(10))+',%' and @xmkeys like '%,'+cast(sfsfywxm_xmkey as varchar(10))+',%' GROUP BY sfyxzd.sfyxzd_yxkey, sfyxzd.sfyxzd_yxmc, sffyxmzd.sffyxmzd_xmmc go--调用:exec up_getbzsfhz_csx '1,2,3,4,5,6,7,8','1,3,5,9,10,11,12,6',45
as exec('SELECT sfyxzd.sfyxzd_yxkey, sfyxzd.sfyxzd_yxmc, ffyxmzd.sffyxmzd_xmmc,
count(sfsfyw.sfsfyw_xskey) as count1, sum(sfsfywxm.sfsfywxm_je)
as je FROM sfsfyw, sfsfywxm, sffyxmzd, sfxszd, sfyxzd WHERE
( sfsfyw.sfsfyw_sfkey = sfsfywxm.sfsfywxm_sfkey ) AND
( sfsfyw.sfsfyw_xskey = sfxszd.sfxszd_xskey ) AND
( sfyxzd.sfyxzd_yxkey = sfxszd.sfxszd_yxkey ) AND
( sfsfywxm.sfsfywxm_xmkey = sffyxmzd.sffyxmzd_xmkey )
and (sfyxzd_yxkey in ('+@yxkeys+') ) and ( sfsfywxm_xmkey in ('+@xmkeys+' ) )
GROUP BY sfyxzd.sfyxzd_yxkey, sfyxzd.sfyxzd_yxmc, sffyxmzd.sffyxmzd_xmmc ')go--调用:exec up_getbzsfhz_csx '1,2,3,4,5,6,7,8','1,3,5,9,10,11,12,6',45
得到你想要的字符串啊
(@xmkeys varchar(100), @yxkeys varchar(100), @fylx char(5))
as
SELECT sfyxzd.sfyxzd_yxkey, sfyxzd.sfyxzd_yxmc, ffyxmzd.sffyxmzd_xmmc, count(sfsfyw.sfsfyw_xskey) as count1, sum(sfsfywxm.sfsfywxm_je) as je
FROM sfsfyw, sfsfywxm, sffyxmzd, sfxszd, sfyxzd
WHERE ( sfsfyw.sfsfyw_sfkey = sfsfywxm.sfsfywxm_sfkey )
AND ( sfsfyw.sfsfyw_xskey = sfxszd.sfxszd_xskey )
AND ( sfyxzd.sfyxzd_yxkey = sfxszd.sfxszd_yxkey )
AND ( sfsfywxm.sfsfywxm_xmkey = sffyxmzd.sffyxmzd_xmkey )
and charindex(','+ltrim(rtrim(cast(sfyxzd_yxkey as char)))+',',','+@yxkeys+',')>0
and charindex(','+ltrim(rtrim(cast(sfsfywxm_xmkey as char)))+',',','+@xmkeys+',')>0
GROUP BY sfyxzd.sfyxzd_yxkey, sfyxzd.sfyxzd_yxmc, sffyxmzd.sffyxmzd_xmmc
as exec('SELECT sfyxzd.sfyxzd_yxkey, sfyxzd.sfyxzd_yxmc, ffyxmzd.sffyxmzd_xmmc,
count(sfsfyw.sfsfyw_xskey) as count1, sum(sfsfywxm.sfsfywxm_je)
as je FROM sfsfyw, sfsfywxm, sffyxmzd, sfxszd, sfyxzd WHERE
( sfsfyw.sfsfyw_sfkey = sfsfywxm.sfsfywxm_sfkey ) AND
( sfsfyw.sfsfyw_xskey = sfxszd.sfxszd_xskey ) AND
( sfyxzd.sfyxzd_yxkey = sfxszd.sfxszd_yxkey ) AND
( sfsfywxm.sfsfywxm_xmkey = sffyxmzd.sffyxmzd_xmkey )
and (sfyxzd_yxkey in ('+@yxkeys+') ) and ( sfsfywxm_xmkey in ('+@xmkeys+' ) )
GROUP BY sfyxzd.sfyxzd_yxkey, sfyxzd.sfyxzd_yxmc, sffyxmzd.sffyxmzd_xmmc ')go--调用:exec up_getbzsfhz_csx '1,2,3,4,5,6,7,8','1,3,5,9,10,11,12,6',45
create proc up_getbzsfhz_csx (@xmkeys varchar(100), @yxkeys varchar(100), @fylx char(5))
as
........
as SELECT sfyxzd.sfyxzd_yxkey, sfyxzd.sfyxzd_yxmc, ffyxmzd.sffyxmzd_xmmc,
count(sfsfyw.sfsfyw_xskey) as count1, sum(sfsfywxm.sfsfywxm_je)
as je FROM sfsfyw, sfsfywxm, sffyxmzd, sfxszd, sfyxzd WHERE
( sfsfyw.sfsfyw_sfkey = sfsfywxm.sfsfywxm_sfkey ) AND
( sfsfyw.sfsfyw_xskey = sfxszd.sfxszd_xskey ) AND
( sfyxzd.sfyxzd_yxkey = sfxszd.sfxszd_yxkey ) AND
( sfsfywxm.sfsfywxm_xmkey = sffyxmzd.sffyxmzd_xmkey )
and @yxkeys like '%,'+cast(sfyxzd_yxkey as varchar(10))+',%' and @xmkeys like '%,'+cast(sfsfywxm_xmkey as varchar(10))+',%'
GROUP BY sfyxzd.sfyxzd_yxkey, sfyxzd.sfyxzd_yxmc, sffyxmzd.sffyxmzd_xmmc go--调用:exec up_getbzsfhz_csx '1,2,3,4,5,6,7,8','1,3,5,9,10,11,12,6',45
as SELECT sfyxzd.sfyxzd_yxkey, sfyxzd.sfyxzd_yxmc, ffyxmzd.sffyxmzd_xmmc,
count(sfsfyw.sfsfyw_xskey) as count1, sum(sfsfywxm.sfsfywxm_je)
as je FROM sfsfyw, sfsfywxm, sffyxmzd, sfxszd, sfyxzd WHERE
( sfsfyw.sfsfyw_sfkey = sfsfywxm.sfsfywxm_sfkey ) AND
( sfsfyw.sfsfyw_xskey = sfxszd.sfxszd_xskey ) AND
( sfyxzd.sfyxzd_yxkey = sfxszd.sfxszd_yxkey ) AND
( sfsfywxm.sfsfywxm_xmkey = sffyxmzd.sffyxmzd_xmkey )
and @yxkeys like '%,'+cast(sfyxzd_yxkey as varchar(10))+',%' and @xmkeys like '%,'+cast(sfsfywxm_xmkey as varchar(10))+',%'
GROUP BY sfyxzd.sfyxzd_yxkey, sfyxzd.sfyxzd_yxmc, sffyxmzd.sffyxmzd_xmmc go--调用:exec up_getbzsfhz_csx '1,2,3,4,5,6,7,8','1,3,5,9,10,11,12,6',45