sql server 2008定义变量长度问题

你把完整的sql 贴出来吧，敏感的字眼改一下就是了

注意：SQLServer 中，能够使用 + 运算的字符串变量最大不能超过 8000，这是硬性规定，虽然你定义的是 varchar(max)。

declare @sql varchar(max)
set @sql=''
set @sql=@sql+'select fs.merchantno,isnull(fs.idcard_sett_count,0) as idcard_sett_count,isnull(fs.card_sett_count,0) as card_sett_count,isnull(fs.area_sett_count,0) as area_sett_count,isnull(fs.amount,0) as amount,isnull(fs.count,0) as count,isnull(fs.dx_count,0) as dx_count,isnull(fs.xzzf_idcard_amount,0) as xzzf_idcard_amount,isnull(fs.xzzf_card_amount,0) as xzzf_card_amount,isnull(fs.bft_idcard_amount,0) as bft_idcard_amount,isnull(fs.bft_card_amount,0) as bft_card_amount,isnull(fs.gzt_idcard_amount,0) as gzt_idcard_amount,isnull(fs.gzt_card_amount,0) as gzt_card_amount,isnull(fs.gy_idcard_amount,0) as gy_idcard_amount,isnull(fs.gy_card_amount,0) as gy_card_amount,isnull(fs.dx_amount,0) as dx_amount,isnull(fs.bft_area_amount,0) as bft_area_amount,isnull(fs.yf_area_amount,0) as yf_area_amount, fs.merchantname,fs.re3,fs.card_sett_type,fs.idcard_fee,isnull(fs.accountManager,'''') as accountManager,isnull(es.card_price,0) as card_price,isnull(es.fei_count,0) as fei_count,isnull(fs.bft_card_fei_amount,0) as bft_card_fei_amount,isnull(fs.gy_card_fei_amount,0) as gy_card_fei_amount,isnull(fs.hy_idcard_amount,0) as hy_idcard_amount,isnull(fs.hy_idcard_by_amount,0) as hy_idcard_by_amount,isnull(fs.szwr_idcard_amount,0) as szwr_idcard_amount,isnull(fs.jx_idcard_amount,0) as jx_idcard_amount,isnull(fs.jx_card_amount,0) as jx_card_amount,isnull(fs.jx_card_fei_amount,0) as jx_card_fei_amount,isnull(fs.xs_idcard_amount,0) as xs_idcard_amount,isnull(fs.xs_card_fei_amount,0) as xs_card_fei_amount,isnull(fs.xs_card_amount,0) as xs_card_amount from ('
set @sql=@sql+'    select ms.merchantno,isnull(ms.idcard_sett_count,0) as idcard_sett_count,isnull(ms.card_sett_count,0) as card_sett_count,isnull(ms.area_sett_count,0) as area_sett_count,isnull(ms.amount,0) as amount,isnull(ms.count,0) as count,isnull(ms.dx_count,0) as dx_count,isnull(ms.xzzf_idcard_amount,0) as xzzf_idcard_amount,isnull(ms.xzzf_card_amount,0) as xzzf_card_amount,isnull(ms.bft_idcard_amount,0) as bft_idcard_amount,isnull(ms.bft_card_amount,0) as bft_card_amount, isnull(ms.gzt_idcard_amount,0) as gzt_idcard_amount,isnull(ms.gzt_card_amount,0) as gzt_card_amount,isnull(ms.gy_idcard_amount,0) as gy_idcard_amount,isnull(ms.gy_card_amount,0) as gy_card_amount,isnull(ms.dx_amount,0) as dx_amount,isnull(ms.bft_area_amount,0) as bft_area_amount,isnull(ms.yf_area_amount,0) as yf_area_amount, m.merchantname,m.re3,m.card_sett_type,m.idcard_fee,m.idcard_fee_rate,m.card_fee,m.card_fee_rate,m.mes_fee,isnull(m.accountManager,'''') as accountManager,ms.bft_card_fei_amount,ms.gy_card_fei_amount,ms.hy_idcard_amount,ms.hy_idcard_by_amount,ms.szwr_idcard_amount,ms.jx_idcard_amount,ms.jx_card_amount,ms.jx_card_fei_amount,ms.xs_idcard_amount,ms.xs_card_fei_amount,ms.xs_card_amount from ('
set @sql=@sql+'        select isnull(sc.merchantno,sd.merchantno) as merchantno,sc.idcard_sett_count,sc.card_sett_count,sc.area_sett_count,sc.amount,sc.count,sc.dx_count,sd.xzzf_idcard_amount,sd.xzzf_card_amount,sd.bft_idcard_amount,sd.bft_card_amount,sd.gzt_idcard_amount,sd.gzt_card_amount,sd.gy_idcard_amount,sd.gy_card_amount,sd.dx_amount,sd.bft_area_amount,sd.yf_area_amount,sd.bft_card_fei_amount,sd.gy_card_fei_amount,sd.hy_idcard_amount,sd.hy_idcard_by_amount,sd.szwr_idcard_amount,sd.jx_idcard_amount,sd.jx_card_amount,sd.jx_card_fei_amount,sd.xs_idcard_amount,sd.xs_card_fei_amount,sd.xs_card_amount from ('
set @sql=@sql+'        select d.merchantno,d.idcard_sett_count,d.card_sett_count,d.area_sett_count,d.amount,d.count,dx.dx_count from ('
set @sql=@sql+'            select ss.merchantno,ss.idcard_sett_count,ss.card_sett_count,ss.area_sett_count,r.amount,r.count from('
set @sql=@sql+'                select jq.merchantno,jq.idcard_sett_count,jq.card_sett_count,gs.area_sett_count from ('
set @sql=@sql+'                    select merchantno,isnull([01],0) as idcard_sett_count,isnull([02],0) as card_sett_count from ('
set @sql=@sql+'                        select merchantno,trantype,count(*) as count from transdetail where orderstate='''+'00'+''' and trandate like '''+@date +'%'+''' and trantype<>'''' and (tranCode = '''+'00'+''' or tranCode is null) group by merchantno,trantype'
set @sql=@sql+'                    ) a pivot(min([count]) for trantype in([01],[02])) pvt'
set @sql=@sql+'                ) jq full join'
set @sql=@sql+'                ('
set @sql=@sql+'                    select merchantno,count(*) as area_sett_count from transdetail where (areaorderstate='''+'00'+''' and (areaResponseCode='''+'0000'+''' or areaResponseCode='''+'0001'+''')) and trandate like '''+@date +'%'+''' group by merchantno'
set @sql=@sql+'                ) gs on jq.merchantno=gs.merchantno'
set @sql=@sql+'            ) ss full join(select merchantno,sum(tranamount) as amount,count(*) as count from rechargeDetails r where trandate like '''+@date +'%'+''' and flag='''+'1'+''' group by r.merchantno) r on ss.merchantno=r.merchantno'
set @sql=@sql+'         ) d full join (select merchantno,count(*) as dx_count from transdetail t where t.tranType='''+'03'+''' and t.channel='''+'999'+''' and t.trandate like '''+@date +'%'+''' group by merchantno) dx on d.merchantno=dx.merchantno'
set @sql=@sql+'         ) sc full join ('
set @sql=@sql+'    select isnull(idcard.merchantno,gs.merchantno) as merchantno,idcard.xzzf_idcard_amount,idcard.xzzf_card_amount,idcard.bft_idcard_amount,idcard.bft_card_amount,idcard.gzt_idcard_amount,idcard.gzt_card_amount,idcard.gy_idcard_amount,idcard.gy_card_amount,isnull(bft_area_amount,0) as bft_area_amount,bft_card_fei_amount,gy_card_fei_amount,hy_idcard_amount,hy_idcard_by_amount,isnull(gs.yf_area_amount,0) yf_area_amount,idcard.dx_amount,idcard.szwr_idcard_amount,idcard.jx_idcard_amount,idcard.jx_card_amount,idcard.jx_card_fei_amount,idcard.xs_idcard_amount,idcard.xs_card_fei_amount,idcard.xs_card_amount from ('
--set @sql=@sql+'        --身份验证，卡验证,短信成本'
set @sql=@sql+'        select merchantno,isnull([10001],0) as xzzf_idcard_amount,isnull([10002],0) as xzzf_card_amount,isnull([20001],0) as bft_idcard_amount,isnull([20002],0) as bft_card_amount,isnull([70001],0) as gzt_idcard_amount,isnull([70002],0) as gzt_card_amount,isnull([80001],0) as gy_idcard_amount,isnull([80002],0) as gy_card_amount,isnull([99903],0) as dx_amount,isnull([20102],0) as bft_card_fei_amount,isnull([80102],0) as gy_card_fei_amount,isnull([90101],0) as hy_idcard_amount,isnull([90201],0) as hy_idcard_by_amount,isnull([60001],0) as szwr_idcard_amount,isnull([22101],0) as jx_idcard_amount,isnull([22202],0) as jx_card_fei_amount,isnull([22302],0) as jx_card_amount,isnull([66501],0) as xs_idcard_amount,isnull([66602],0) as xs_card_fei_amount,isnull([66702],0) as xs_card_amount from ('
set @sql=@sql+'            select ss.merchantno,ss.ctype,SUM(ss.baseamount) as [base_amount] from ('
set @sql=@sql+'                select s.merchantno,(s.channel+s.tranType) as ctype,(s.count*s.amount) as baseamount from ('
set @sql=@sql+'                    select t.merchantno,t.channel,t.tranType,t.backState, t.count,l.amount from ('
set @sql=@sql+'                        select merchantno,channel,trantype,backstate, count(*) as count from transdetail'
set @sql=@sql+'                        where orderState='''+'00'+''''
set @sql=@sql+'                        and tranDate like '''+@date +'%'+''' and (tranCode = 00 or tranCode is null) '
set @sql=@sql+'                        group by merchantno,channel,tranType,backState'
set @sql=@sql+'                    )t,'
set @sql=@sql+'                    (select merchantno,SUBSTRING(interval,1,CHARINDEX('''+'-'+''',interval)-1) as startamt,SUBSTRING(interval,CHARINDEX('''+'-'+''',interval)+1,LEN(interval)) as endamt,amount,type from ladder l where merType='''+'1'+''') l,'以上只是一部分，那就是没有办法使用存储过程了

这么长的 SQL 语句，是否可以拆分成几个子句，借助临时表或表变量来处理？

现在的sql语句是通过程序传进来，准备改成存储过程，发现以上问题

你搞的太麻烦了。
把它写成：
WITH AS，子查询结构（subquery factoring）再放入proc就可以了。

是需要改，我自己也觉得不满意把每个select做成子查询，把变量放到where。
再把所有的子查询，进行合集。写成proc就可以满足你的需求

调试易

sql server 2008定义变量长度问题

解决方案 »