[Syteline]动态参数的存储过程

由于实际开发过程中需要用到动态的改变 SQL语句.

动态列,动态条件限制

create procedure name(

@para 1...

@para.2..

....

)

begin

declare @sql='select * from tbname where ...'

set @sql=@sql+

case when <condi..> then '..'else ' and col=@para1' end

.....

EXEC (@sql)

end

在做试算平衡表的过程中

我的写法:

Create Procedure UF_Rpt_Trialbalance(

@site nvarchar(8)=null

,@Staringyear nvarchar(4)=null

,@Endingyear nvarchar(4)=null

,@Staringmonth nvarchar(2)=null

,@Endingmonth nvarchar(2)=null

,@u1 bit

,@u2 bit

,@u3 bit

,@u4 bit

)

As

declare

@u1 bit

,@u2 bit

,@u3 bit

,@u4 bit

set @u1 =1

set @u2 =1

set @u3 =1

set @u4 =1

--------------------------declare------------------------

declare @uf_fileds varchar(200)

,@sql varchar(2000)

set @uf_fileds='acct'

if @u1=1

set @uf_fileds=@uf_fileds+',acct_unit1'

if @u2=1

set @uf_fileds=@uf_fileds+',acct_unit2'

if @u3=1

set @uf_fileds=@uf_fileds+',acct_unit3'

if @u4=1

set @uf_fileds=@uf_fileds+',acct_unit4'

/*

*/

set @sql=

'SELECT j.acct'+case when @u1=1 then ',j.acct_unit1' else '' end

+case when @u2=1 then ',j.acct_unit2' else '' end

+case when @u3=1 then ',j.acct_unit3' else '' end

+case when @u4=1 then ',j.acct_unit4' else '' end

+',e.dom_amount_p,d.dom_amount_Debit,b.dom_amount_Credit from '

+'(SELECT '+@uf_fileds

+' FROM journal '

+' group by '+@uf_fileds+') j'

+' left join (SELECT '+@uf_fileds+',sum(dom_amount) as dom_amount_p '

+' FROM journal '

+' where control_site=''SHAT'' AND control_year<=''2008'' AND control_period<''10'''

+' group by '+@uf_fileds+') e '

+' on e.acct=j.acct'

+case when @u1=1 then ' and e.acct_unit1=j.acct_unit1' else '' end

+case when @u2=1 then ' and e.acct_unit2=j.acct_unit2' else '' end

+case when @u3=1 then ' and e.acct_unit3=j.acct_unit3' else '' end

+case when @u4=1 then ' and e.acct_unit4=j.acct_unit4' else '' end

+' left join (SELECT '+@uf_fileds+',sum(dom_amount) as dom_amount_Debit '

+' FROM journal'

+' where control_site=''SHAT'' AND control_year=''2008'' AND control_period=''10''and dom_amount<0'

+' group by '+@uf_fileds+' ) d'

+' on j.acct=d.acct'

+case when @u1=1 then ' and j.acct_unit1=d.acct_unit1' else '' end

+case when @u2=1 then ' and j.acct_unit2=d.acct_unit2' else '' end

+case when @u3=1 then ' and j.acct_unit3=d.acct_unit3' else '' end

+case when @u4=1 then ' and j.acct_unit4=d.acct_unit4' else '' end

+' left join (SELECT '+@uf_fileds+',sum(dom_amount) as dom_amount_Credit '

+' FROM journal'

+' where control_site=''SHAT'' AND control_year=''2008'' AND control_period=''10'' AND dom_amount>0'

+' group by '+@uf_fileds+') B '

+' ON j.acct=b.acct'

+case when @u1=1 then ' and j.acct_unit1=b.acct_unit1' else '' end

+case when @u2=1 then ' and j.acct_unit2=b.acct_unit2' else '' end

+case when @u3=1 then ' and j.acct_unit3=b.acct_unit3' else '' end

+case when @u4=1 then ' and j.acct_unit4=b.acct_unit4' else '' end

print @sql

我们老大的写法:

CREATE PROCEDURE dbo.UF_CreateLedgerTmpTableSp (

@acct as nvarchar(10)=null

,@unit1 as nvarchar(30)=null

,@unit2 as nvarchar(30)=null

,@unit3 as nvarchar(30)=null

,@unit4 as nvarchar(30)=null

,@su1 as bit=0

,@su2 as bit=0

,@su3 as bit=0

,@su4 as bit=0

,@year as int=null

,@period as int=null

,@unposted as bit=0

,@LocalId as varchar(50)=null

)as

begin

Declare

@sql nvarchar(4000)

,@groupby nvarchar(1000)

,@table nvarchar(2000)

set @groupby='acct'

if @su1=1

set @groupby=@groupby + ',acct_unit1'

if @su2=1

set @groupby=@groupby + ',acct_unit2'

if @su3=1

set @groupby=@groupby + ',acct_unit3'

if @su4=1

set @groupby=@groupby + ',acct_unit4'

set @table='Ledger'

if @unposted=1

set @table='(select acct,acct_unit1,acct_unit2,acct_unit3,acct_unit4,curr_code,for_amount,dom_amount,control_year,control_period,from_id from ledger

union

select acct,acct_unit1,acct_unit2,acct_unit3,acct_unit4,curr_code,for_amount,dom_amount,control_year,control_period,id as from_id from journal) a'

set @sql=

'select ' + @groupby + ',curr_code

,sum(case when (for_amount>=0 and control_year=' + str(@year) + ' and control_period= ' + str(@period) + ') then isnull(for_amount,0) else 0 end) as foramt_dr

,sum(case when (for_amount<0 and control_year=' + str(@year) + ' and control_period= ' + str(@period) + ') then isnull(for_amount,0) else 0 end) as foramt_cr

,sum(case when (dom_amount>=0 and control_year=' + str(@year) + ' and control_period= ' + str(@period) + ') then isnull(dom_amount,0) else 0 end) as domamt_dr

,sum(case when (dom_amount<0 and control_year=' + str(@year) + ' and control_period= ' + str(@period) + ') then isnull(dom_amount,0) else 0 end) as domamt_cr

,sum(case when (control_year<=' + str(@year) + ' and control_period<=' + str(@period) + ') then isnull(for_amount,0) else 0 end) as end_foramt

,sum(case when (control_year<=' + str(@year) + ' and control_period<=' + str(@period) + ') then isnull(dom_amount,0) else 0 end) as end_domamt

into uf_tmp

from ' + @table +'

where acct='+ isnull(@acct,'acct') + '

and from_id=' + isnull(@Localid,'from_id') + '

and isnull(acct_unit1,1)=' + isnull(@unit1, 'isnull(acct_unit1,1)') + '

and isnull(acct_unit2,1)=' + isnull(@unit2, 'isnull(acct_unit2,1)') + '

and isnull(acct_unit3,1)=' + isnull(@unit3, 'isnull(acct_unit3,1)') + '

and isnull(acct_unit4,1)=' + isnull(@unit4, 'isnull(acct_unit4,1)') + '

group by ' + @groupby + ',curr_code'

IF object_id('[dbo].uf_tmp') IS NOT NULL --判断临时表#tmp是否存在,存在则删除

drop table uf_tmp

EXEC (@sql)

select * from uf_tmp

end

GO

明显感觉到自己不够简洁,代码繁琐,效率不高.希望后续多多提高..