以下存储过程如何优化？

alter proc ZZBI0028LJWKP

@ks varchar(10),

@js varchar(10),

@gs varchar(3),

@bm varchar(20)

as

begin

--6.10 未开票数

--select * from BillsOfLading --交货单表

--select * from BOLItems --交货单分录

--select InventoryQuantity,DeQuantity,OffQuantity,OffInvQuantity,ReturnQuantity from BOLItems 

--select BOLID,sum((DeQuantity-InventoryQuantity)*taxinprice) a from BOLItems a group by BOLID





declare @where varchar(2000)

set @where='where 1=1';

if(ltrim(@ks)!='')

begin

set @where=@where+' and rq1 >= '''+@ks+''' ';

end

if(ltrim(@js)!='')

begin

set @where=@where+' and rq1 <= '''+@js+''' ';

end

if(ltrim(@gs)!='')

begin

set @where=@where+' and gs1 = '''+@gs+''' ';

end

if(ltrim(@bm)!='')

begin

set @where=@where+' and bm1 = '''+@bm+''' ';

end

create table #ZZBI0028LJWKP

(

gs varchar(50),

dq varchar(50),

pq varchar(50),

kh varchar(50),

ddbh varchar(20),

hz decimal(20,8),

rq1 varchar(20),

gs1 varchar(10),

bm1 varchar(20)

)

--1:不等于退货类别、部分出库

declare @sql1 varchar(3000)

set @sql1='

insert into #ZZBI0028LJWKP select d.LSBZDW_DWMC as 结算公司,c.LSBMZD_BMMC as 大区,f.DisChannelName as 片区,e.LSWLDW_DWMC as 客户名称,b.CustomField10 as 销售订单编号,(DeQuantity-InventoryQuantity-OffQuantity)*taxinprice aaa,b.BOLDate,b.CompanyID ,b.DepartmentID from BOLItems a,BillsOfLading b,(select a.LSBMZD_BMBH,a.LSBMZD_DWBH,a.LSBMZD_bmmc from lsbmzd a where LSBMZD_JS=1 ) c,LSBZDW d,LSWLDW e,DISCHANNELS f

where a.BOLID=b.BOLID and (DeQuantity-InventoryQuantity-OffQuantity)*taxinprice!=0

and a.CompanyID=c.LSBMZD_DWBH and LEFT(b.DepartmentID,3)=c.LSBMZD_BMBH

and b.CompanyID=d.LSBZDW_DWBH

and b.ShipToParty=e.LSWLDW_WLDWBH

and b.DisChannelID=f.DisChannelID

and ItemCaCode !=''REN''

and a.InventoryFlag !=0

'

exec (@sql1)

--2:退货、部分出库

declare @sql2 varchar(3000)

set @sql2='

insert into #ZZBI0028LJWKP select d.LSBZDW_DWMC as 结算公司,c.LSBMZD_BMMC as 大区,f.DisChannelName as 片区,e.LSWLDW_DWMC as 客户名称,b.CustomField10 as 销售订单编号,-(DeQuantity-InventoryQuantity-OffQuantity)*taxinprice aaa,b.BOLDate,b.CompanyID,b.DepartmentID from BOLItems a,BillsOfLading b,(select a.LSBMZD_BMBH,a.LSBMZD_DWBH,a.LSBMZD_bmmc from lsbmzd a where LSBMZD_JS=1 ) c,LSBZDW d,LSWLDW e,DISCHANNELS f

where a.BOLID=b.BOLID and -(DeQuantity-InventoryQuantity-OffQuantity)*taxinprice!=0

and a.CompanyID=c.LSBMZD_DWBH and LEFT(b.DepartmentID,3)=c.LSBMZD_BMBH

and b.CompanyID=d.LSBZDW_DWBH

and b.ShipToParty=e.LSWLDW_WLDWBH

and b.DisChannelID=f.DisChannelID

and ItemCaCode =''REN''

and a.InventoryFlag !=0

'

exec (@sql2)

--3:不等于退货类别、未出库

declare @sql3 varchar(3000)

set @sql3='

insert into #ZZBI0028LJWKP select d.LSBZDW_DWMC as 结算公司,c.LSBMZD_BMMC as 大区,f.DisChannelName as 片区,e.LSWLDW_DWMC as 客户名称,b.CustomField10 as 销售订单编号,DeQuantity*taxinprice aaa,b.BOLDate,b.CompanyID,b.DepartmentID from BOLItems a,BillsOfLading b,(select a.LSBMZD_BMBH,a.LSBMZD_DWBH,a.LSBMZD_bmmc from lsbmzd a where LSBMZD_JS=1 ) c,LSBZDW d,LSWLDW e,DISCHANNELS f

where a.BOLID=b.BOLID 

and a.CompanyID=c.LSBMZD_DWBH and LEFT(b.DepartmentID,3)=c.LSBMZD_BMBH

and b.CompanyID=d.LSBZDW_DWBH

and b.ShipToParty=e.LSWLDW_WLDWBH

and b.DisChannelID=f.DisChannelID

and ItemCaCode !=''REN''

and a.InventoryFlag =0

'

exec (@sql3)

--4:退货类别、未出库

declare @sql4 varchar(3000)

set @sql4='

insert into #ZZBI0028LJWKP select d.LSBZDW_DWMC as 结算公司,c.LSBMZD_BMMC as 大区,f.DisChannelName as 片区,e.LSWLDW_DWMC as 客户名称,

b.CustomField10 as 销售订单编号,-DeQuantity*taxinprice aaa ,b.BOLDate,b.CompanyID ,b.DepartmentID 

from BillsOfLading b,BOLItems a,(select a.LSBMZD_BMBH,a.LSBMZD_DWBH,a.LSBMZD_bmmc from lsbmzd a where LSBMZD_JS=1 ) c,

LSBZDW d,LSWLDW e,DISCHANNELS f

where a.BOLID=b.BOLID and -DeQuantity*taxinprice!=0

and a.CompanyID=c.LSBMZD_DWBH and LEFT(b.DepartmentID,3)=c.LSBMZD_BMBH

and b.CompanyID=d.LSBZDW_DWBH

and b.ShipToParty=e.LSWLDW_WLDWBH

and b.DisChannelID=f.DisChannelID

and ItemCaCode =''REN''

and a.InventoryFlag =0

'

exec (@sql4)





declare @sql varchar(3000)

set @sql='

select gs,dq,pq,kh,ddbh,hz from #ZZBI0028LJWKP

'+@where

exec(@sql)

drop table #ZZBI0028LJWKP



end