SQL Server sql中参数过多利用变量替换参数的方法

这篇文章主要为大家详细介绍了SQL Server sql中参数过多利用变量替换参数的方法，具有一定的参考价值，可以用来参考一下。

感兴趣的小伙伴，下面一起跟随四海网的小编两巴掌来看看吧！

工作中遇到一个需求，需要非常多的参数，例如如下sql，

代码如下:

 
select 
ff.fundsc||'-'||ff.fundtzfs||'-'||ff.fundcjfl||'-'||ff.fundonefl||'-'||ff.fundtowfl catagory, 
sf.scode,replace(sf.fund5,'型证券投资基金','')fund5,sf.fund4,sf.fund10, 
(select to_date(tradedate,'yyyy-MM-dd') from sdc_fundnetassetvalue where scode=sf.scode and tradedate in 
(select max(tradedate) from sdc_fundnetassetvalue where scode=sf.scode and to_date(tradedate,'yyyyMMdd')>=(to_date(20120801,'yyyyMMdd')-10) and tradedate<=20120801) and rownum<2) trdatedate, 
(select unitvalue from sdc_fundnetassetvalue where scode=sf.scode and tradedate in 
(select max(tradedate) from sdc_fundnetassetvalue where scode=sf.scode and to_date(tradedate,'yyyyMMdd')>=(to_date(20120801,'yyyyMMdd')-10) and tradedate<=20120801) and rownum<2) fejz, 
(select accumulatedunitvalue from sdc_fundnetassetvalue where scode=sf.scode and tradedate in 
(select max(tradedate) from sdc_fundnetassetvalue where scode=sf.scode and to_date(tradedate,'yyyyMMdd')>=(to_date(20120801,'yyyyMMdd')-10) and tradedate<=20120801) and rownum<2) feljjz, 
(select f30004_30002 from si_fund_30002 where f_fundcode=sf.scode and f_type=0 and f_tradingdate in 
(select max(f_tradingdate) from si_fund_30002 where f_fundcode=sf.scode and to_date(f_tradingdate,'yyyyMMdd')>=(to_date(20120801,'yyyyMMdd')-10) and f_tradingdate<=20120801) and rownum<2) gqyzjzzzl, 
(select f30006_30002 from si_fund_30002 where f_fundcode=sf.scode and f_type=0 and f_tradingdate in 
(select max(f_tradingdate) from si_fund_30002 where f_fundcode=sf.scode and to_date(f_tradingdate,'yyyyMMdd')>=(to_date(20120801,'yyyyMMdd')-10) and f_tradingdate<=20120801) and rownum<2) gqyyjzzzl, 
decode((select f30007_30002 from si_fund_30002 where f_fundcode=sf.scode and f_type=0 and f_tradingdate in 
(select max(f_tradingdate) from si_fund_30002 where f_fundcode=sf.scode and to_date(f_tradingdate,'yyyyMMdd')>=(to_date(20120801,'yyyyMMdd')-10) and f_tradingdate<=20120801) and rownum<2), 
null,'-',to_char((select f30007_30002 from si_fund_30002 where f_fundcode=sf.scode and f_type=0 and f_tradingdate in 
(select max(f_tradingdate) from si_fund_30002 where f_fundcode=sf.scode and to_date(f_tradingdate,'yyyyMMdd')>=(to_date(20120801,'yyyyMMdd')-10) and f_tradingdate<=20120801) and rownum<2),'fm999999990.9999')) gqsgyjzzzl, 
row_number() over(partition by ff.fundsc||'-'||ff.fundtzfs||'-'||ff.fundcjfl||'-'||ff.fundonefl||'-'||ff.fundtowfl 
order by decode((select f30007_30002 from si_fund_30002 where f_fundcode=sf.scode and f_type=0 and f_tradingdate in 
(select max(f_tradingdate) from si_fund_30002 where f_fundcode=sf.scode and to_date(f_tradingdate,'yyyyMMdd')>=(to_date(20120801,'yyyyMMdd')-10) and f_tradingdate<=20120801) and rownum<2), 
null,'-',to_char((select f30007_30002 from si_fund_30002 where f_fundcode=sf.scode and f_type=0 and f_tradingdate in 
(select max(f_tradingdate) from si_fund_30002 where f_fundcode=sf.scode and to_date(f_tradingdate,'yyyyMMdd')>=(to_date(20120801,'yyyyMMdd')-10) and f_tradingdate<=20120801) and rownum<2),'fm999999990.9999')) desc) sgy, 
decode((select f30008_30002 from si_fund_30002 where f_fundcode=sf.scode and f_type=0 and f_tradingdate in 
(select max(f_tradingdate) from si_fund_30002 where f_fundcode=sf.scode and to_date(f_tradingdate,'yyyyMMdd')>=(to_date(20120801,'yyyyMMdd')-10) and f_tradingdate<=20120801) and rownum<2), 
null,'-',to_char((select f30008_30002 from si_fund_30002 where f_fundcode=sf.scode and f_type=0 and f_tradingdate in 
(select max(f_tradingdate) from si_fund_30002 where f_fundcode=sf.scode and to_date(f_tradingdate,'yyyyMMdd')>=(to_date(20120801,'yyyyMMdd')-10) and f_tradingdate<=20120801) and rownum<2),'fm999999990.9999')) gqbnjzzzl, 
row_number() over(partition by ff.fundsc||'-'||ff.fundtzfs||'-'||ff.fundcjfl||'-'||ff.fundonefl||'-'||ff.fundtowfl 
order by decode((select f30008_30002 from si_fund_30002 where f_fundcode=sf.scode and f_type=0 and f_tradingdate in 
(select max(f_tradingdate) from si_fund_30002 where f_fundcode=sf.scode and to_date(f_tradingdate,'yyyyMMdd')>=(to_date(20120801,'yyyyMMdd')-10) and f_tradingdate<=20120801) and rownum<2), 
null,'-',to_char((select f30008_30002 from si_fund_30002 where f_fundcode=sf.scode and f_type=0 and f_tradingdate in 
(select max(f_tradingdate) from si_fund_30002 where f_fundcode=sf.scode and to_date(f_tradingdate,'yyyyMMdd')>=(to_date(20120801,'yyyyMMdd')-10) and f_tradingdate<=20120801) and rownum<2),'fm999999990.9999')) desc) bn, 
decode((select F30017_30003 from si_fund_30003 where f_fundcode=sf.scode and f_type=0 and f_tradingdate in 
(select max(f_tradingdate) from si_fund_30003 where f_fundcode=sf.scode and to_date(f_tradingdate,'yyyyMMdd')>=(to_date(20120801,'yyyyMMdd')-10) and f_tradingdate<=20120801) and rownum<2), 
null,'-',to_char((select F30017_30003 from si_fund_30003 where f_fundcode=sf.scode and f_type=0 and f_tradingdate in 
(select max(f_tradingdate) from si_fund_30003 where f_fundcode=sf.scode and to_date(f_tradingdate,'yyyyMMdd')>=(to_date(20120801,'yyyyMMdd')-10) and f_tradingdate<=20120801) and rownum<2),'fm999999990.9999')) jinnian, 
row_number() over(partition by ff.fundsc||'-'||ff.fundtzfs||'-'||ff.fundcjfl||'-'||ff.fundonefl||'-'||ff.fundtowfl 
order by decode((select F30017_30003 from si_fund_30003 where f_fundcode=sf.scode and f_type=0 and f_tradingdate in 
(select max(f_tradingdate) from si_fund_30003 where f_fundcode=sf.scode and to_date(f_tradingdate,'yyyyMMdd')>=(to_date(20120801,'yyyyMMdd')-10) and f_tradingdate<=20120801) and rownum<2), 
null,'-',to_char((select F30017_30003 from si_fund_30003 where f_fundcode=sf.scode and f_type=0 and f_tradingdate in 
(select max(f_tradingdate) from si_fund_30003 where f_fundcode=sf.scode and to_date(f_tradingdate,'yyyyMMdd')>=(to_date(20120801,'yyyyMMdd')-10) and f_tradingdate<=20120801) and rownum<2),'fm999999990.9999')) desc) jn, 
decode((select f30009_30002 from si_fund_30002 where f_fundcode=sf.scode and f_type=0 and f_tradingdate in 
(select max(f_tradingdate) from si_fund_30002 where f_fundcode=sf.scode and to_date(f_tradingdate,'yyyyMMdd')>=(to_date(20120801,'yyyyMMdd')-10) and f_tradingdate<=20120801) and rownum<2), 
null,'-',to_char((select f30009_30002 from si_fund_30002 where f_fundcode=sf.scode and f_type=0 and f_tradingdate in 
(select max(f_tradingdate) from si_fund_30002 where f_fundcode=sf.scode and to_date(f_tradingdate,'yyyyMMdd')>=(to_date(20120801,'yyyyMMdd')-10) and f_tradingdate<=20120801) and rownum<2),'fm999999990.9999')) gqynjzzzl, 
row_number() over(partition by ff.fundsc||'-'||ff.fundtzfs||'-'||ff.fundcjfl||'-'||ff.fundonefl||'-'||ff.fundtowfl 
order by decode((select f30009_30002 from si_fund_30002 where f_fundcode=sf.scode and f_type=0 and f_tradingdate in 
(select max(f_tradingdate) from si_fund_30002 where f_fundcode=sf.scode and to_date(f_tradingdate,'yyyyMMdd')>=(to_date(20120801,'yyyyMMdd')-10) and f_tradingdate<=20120801) and rownum<2), 
null,'-',to_char((select f30009_30002 from si_fund_30002 where f_fundcode=sf.scode and f_type=0 and f_tradingdate in 
(select max(f_tradingdate) from si_fund_30002 where f_fundcode=sf.scode and to_date(f_tradingdate,'yyyyMMdd')>=(to_date(20120801,'yyyyMMdd')-10) and f_tradingdate<=20120801) and rownum<2),'fm999999990.9999')) desc) gqyn, 
decode((select f30010_30002 from si_fund_30002 where f_fundcode=sf.scode and f_type=0 and f_tradingdate in 
(select max(f_tradingdate) from si_fund_30002 where f_fundcode=sf.scode and to_date(f_tradingdate,'yyyyMMdd')>=(to_date(20120801,'yyyyMMdd')-10) and f_tradingdate<=20120801) and rownum<2), 
null,'-',to_char((select f30010_30002 from si_fund_30002 where f_fundcode=sf.scode and f_type=0 and f_tradingdate in 
(select max(f_tradingdate) from si_fund_30002 where f_fundcode=sf.scode and to_date(f_tradingdate,'yyyyMMdd')>=(to_date(20120801,'yyyyMMdd')-10) and f_tradingdate<=20120801) and rownum<2),'fm999999990.9999')) gqlnjzzzl, 
row_number() over(partition by ff.fundsc||'-'||ff.fundtzfs||'-'||ff.fundcjfl||'-'||ff.fundonefl||'-'||ff.fundtowfl 
order by decode((select f30010_30002 from si_fund_30002 where f_fundcode=sf.scode and f_type=0 and f_tradingdate in 
(select max(f_tradingdate) from si_fund_30002 where f_fundcode=sf.scode and to_date(f_tradingdate,'yyyyMMdd')>=(to_date(20120801,'yyyyMMdd')-10) and f_tradingdate<=20120801) and rownum<2), 
null,'-',to_char((select f30010_30002 from si_fund_30002 where f_fundcode=sf.scode and f_type=0 and f_tradingdate in 
(select max(f_tradingdate) from si_fund_30002 where f_fundcode=sf.scode and to_date(f_tradingdate,'yyyyMMdd')>=(to_date(20120801,'yyyyMMdd')-10) and f_tradingdate<=20120801) and rownum<2),'fm999999990.9999')) desc) gqln, 
decode((select f30011_30002 from si_fund_30002 where f_fundcode=sf.scode and f_type=0 and f_tradingdate in 
(select max(f_tradingdate) from si_fund_30002 where f_fundcode=sf.scode and to_date(f_tradingdate,'yyyyMMdd')>=(to_date(20120801,'yyyyMMdd')-10) and f_tradingdate<=20120801) and rownum<2), 
null,'-',to_char((select f30011_30002 from si_fund_30002 where f_fundcode=sf.scode and f_type=0 and f_tradingdate in 
(select max(f_tradingdate) from si_fund_30002 where f_fundcode=sf.scode and to_date(f_tradingdate,'yyyyMMdd')>=(to_date(20120801,'yyyyMMdd')-10) and f_tradingdate<=20120801) and rownum<2),'fm999999990.9999')) gqsnjzzzl 

from 
(select 
b.scode,b.fund5,b.fund4,b.fund10 
from 
sdc_fundbase b left join sdc_security a on b.scode=a.scode and b.mktcode=a.mktcode and b.fund49=a.stype 
where 
b.scode in (select t.fundcode from fund_fundcategoryone t where (t.fundtzfs='开放式' or t.fundtzfs='封闭式') and t.fundonefl!='货币市场基金' and t.fundonefl!='交易类' ) 
and (a.enddate>=to_date(20120801,'yyyy-MM-dd') or a.enddate is null or a.enddate=to_date(19000101,'yyyy-MM-dd')) 
and a.stype in (6,7) order by scode 
) sf 
left join fund_fundcategoryone ff on sf.scode = ff.fundcode and ff.fundonefl!='交易类' 

非常复杂，不仔细介绍，此时需要将20120801处全部变成参数传入，，只写参数就得累死我，下面方法可以节省我的好多工作量，在最内部函数中修改select
b.scode,b.fund5,b.fund4,b.fund10,20120801 tdate
from
将20120801用tdate变量来代替，这样传入的参数就用tdate来表示就可以了。

本文来自：http://www.q1010.com/179/8282-0.html

注：关于SQL Server sql中参数过多利用变量替换参数的方法的内容就先介绍到这里，更多相关文章的可以留意四海网的其他信息。

关键词：SQL SERVER