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

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

动态列,动态条件限制

create procedure name(

  @para 1...

  @para.2..

  ....

)

begin

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

 set @sql=@sql+

        case when  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




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