帮忙修改下存储过程

Question

写的太重复了，主要是变量，有时全部为空，有时可以只给几个变量，缩减到一个上面怎么写条件啊，变量初始还赋空值吗？
create PROCedure  proc_fxyxpbxxcxb
(
@pbbh varchar(8)='',
@szckid varchar(20)='',
@jskhbh varchar(30)='',
@fwkhbh varchar(30)='',
@zt varchar(5)='',
@fcrq varchar(20)='',
@hsrq varchar(20)='' 
)as
begin      
/*根据实际报表创建的临时表*/
        create table #temp_fxyxpbxxcxb
        (pbbh varchar(20) default null,
         zt varchar(10) default null,
         szckid varchar(20) default null,
         szck varchar(20) default null,
         jskhbh varchar(30) default null,
         jskhmc varchar(100) default null,
 fwkhbh varchar(30) default null,
         fwkhmc varchar(100) default null,
         fcrq varchar(20) default null,
 hsrq varchar(20) default null,
--         dz varchar(30) default null,
         zhczr varchar(10) default null,
         yj decimal(12,4) default 0,
         syf decimal(12,4) default 0, 
         sl decimal(12,1) default 0, 
         )if(@pbbh<>'')insert into #temp_fxyxpbxxcxb(pbbh,zt,szckid,szck,jskhbh,jskhmc,fwkhbh,fwkhmc,fcrq,hsrq,zhczr,yj,syf,sl)
select a.zzypbda_bh,a.zzypbda_ztbz,c.warehouseid,c.warehousename,a.zzypbda_jskh,b.lswldw_dwmc,a.zzypbda_fwkh,
d.lswldw_dwmc,a.zzypbda_fcrq,a.zzypbda_yhsrq,a.zzypbda_zhczr,a.zzypbda_yj,a.zzypbda_syf,a.zzypbda_sl
from zzypbda as a
left join lswldw as b on b.lswldw_wldwbh=a.zzypbda_jskh
left join warehouses as c on c.warehouseid=a.zzypbda_szck 
left join lswldw as d on d.lswldw_wldwbh=a.zzypbda_fwkh
where a.zzypbda_bh=@pbbh else if(@szckid<>'')
insert into #temp_fxyxpbxxcxb(pbbh,zt,szckid,szck,jskhbh,jskhmc,fwkhbh,fwkhmc,fcrq,hsrq,zhczr,yj,syf,sl)
select a.zzypbda_bh,a.zzypbda_ztbz,c.warehouseid,c.warehousename,a.zzypbda_jskh,b.lswldw_dwmc,a.zzypbda_fwkh,
d.lswldw_dwmc,a.zzypbda_fcrq,a.zzypbda_yhsrq,a.zzypbda_zhczr,a.zzypbda_yj,a.zzypbda_syf,a.zzypbda_sl
from zzypbda as a
left join lswldw as b on b.lswldw_wldwbh=a.zzypbda_jskh
left join warehouses as c on c.warehouseid=a.zzypbda_szck 
left join lswldw as d on d.lswldw_wldwbh=a.zzypbda_fwkh
where c.warehouseid=@szckid else if(@jskhbh<>'')
insert into #temp_fxyxpbxxcxb(pbbh,zt,szckid,szck,jskhbh,jskhmc,fwkhbh,fwkhmc,fcrq,hsrq,zhczr,yj,syf,sl)
select a.zzypbda_bh,a.zzypbda_ztbz,c.warehouseid,c.warehousename,a.zzypbda_jskh,b.lswldw_dwmc,a.zzypbda_fwkh,
d.lswldw_dwmc,a.zzypbda_fcrq,a.zzypbda_yhsrq,a.zzypbda_zhczr,a.zzypbda_yj,a.zzypbda_syf,a.zzypbda_sl
from zzypbda as a
left join lswldw as b on b.lswldw_wldwbh=a.zzypbda_jskh
left join warehouses as c on c.warehouseid=a.zzypbda_szck 
left join lswldw as d on d.lswldw_wldwbh=a.zzypbda_fwkh
where b.lswldw_wldwbh=@jskhbh else if(@fwkhbh<>'')
insert into #temp_fxyxpbxxcxb(pbbh,zt,szckid,szck,jskhbh,jskhmc,fwkhbh,fwkhmc,fcrq,hsrq,zhczr,yj,syf,sl)
select a.zzypbda_bh,a.zzypbda_ztbz,c.warehouseid,c.warehousename,a.zzypbda_jskh,b.lswldw_dwmc,a.zzypbda_fwkh,
d.lswldw_dwmc,a.zzypbda_fcrq,a.zzypbda_yhsrq,a.zzypbda_zhczr,a.zzypbda_yj,a.zzypbda_syf,a.zzypbda_sl
from zzypbda as a
left join lswldw as b on b.lswldw_wldwbh=a.zzypbda_jskh
left join warehouses as c on c.warehouseid=a.zzypbda_szck 
left join lswldw as d on d.lswldw_wldwbh=a.zzypbda_fwkh
where d.lswldw_wldwbh=@fwkhbh else if(@zt<>'')
insert into #temp_fxyxpbxxcxb(pbbh,zt,szckid,szck,jskhbh,jskhmc,fwkhbh,fwkhmc,fcrq,hsrq,zhczr,yj,syf,sl)
select a.zzypbda_bh,a.zzypbda_ztbz,c.warehouseid,c.warehousename,a.zzypbda_jskh,b.lswldw_dwmc,a.zzypbda_fwkh,
d.lswldw_dwmc,a.zzypbda_fcrq,a.zzypbda_yhsrq,a.zzypbda_zhczr,a.zzypbda_yj,a.zzypbda_syf,a.zzypbda_sl
from zzypbda as a
left join lswldw as b on b.lswldw_wldwbh=a.zzypbda_jskh
left join warehouses as c on c.warehouseid=a.zzypbda_szck 
left join lswldw as d on d.lswldw_wldwbh=a.zzypbda_fwkh
where a.zzypbda_ztbz=@zt else if(@fcrq<>'')
insert into #temp_fxyxpbxxcxb(pbbh,zt,szckid,szck,jskhbh,jskhmc,fwkhbh,fwkhmc,fcrq,hsrq,zhczr,yj,syf,sl)
select a.zzypbda_bh,a.zzypbda_ztbz,c.warehouseid,c.warehousename,a.zzypbda_jskh,b.lswldw_dwmc,a.zzypbda_fwkh,
d.lswldw_dwmc,a.zzypbda_fcrq,a.zzypbda_yhsrq,a.zzypbda_zhczr,a.zzypbda_yj,a.zzypbda_syf,a.zzypbda_sl
from zzypbda as a
left join lswldw as b on b.lswldw_wldwbh=a.zzypbda_jskh
left join warehouses as c on c.warehouseid=a.zzypbda_szck 
left join lswldw as d on d.lswldw_wldwbh=a.zzypbda_fwkh
where a.zzypbda_fcrq<=@fcrq else if(@hsrq<>'')
insert into #temp_fxyxpbxxcxb(pbbh,zt,szckid,szck,jskhbh,jskhmc,fwkhbh,fwkhmc,fcrq,hsrq,zhczr,yj,syf,sl)
select a.zzypbda_bh,a.zzypbda_ztbz,c.warehouseid,c.warehousename,a.zzypbda_jskh,b.lswldw_dwmc,a.zzypbda_fwkh,
d.lswldw_dwmc,a.zzypbda_fcrq,a.zzypbda_yhsrq,a.zzypbda_zhczr,a.zzypbda_yj,a.zzypbda_syf,a.zzypbda_sl
from zzypbda as a
left join lswldw as b on b.lswldw_wldwbh=a.zzypbda_jskh
left join warehouses as c on c.warehouseid=a.zzypbda_szck 
left join lswldw as d on d.lswldw_wldwbh=a.zzypbda_fwkh
where a.zzypbda_ztbz='1' and a.zzypbda_yhsrq<=@hsrq
else
insert into #temp_fxyxpbxxcxb(pbbh,zt,szckid,szck,jskhbh,jskhmc,fwkhbh,fwkhmc,fcrq,hsrq,zhczr,yj,syf,sl)
select a.zzypbda_bh,a.zzypbda_ztbz,c.warehouseid,c.warehousename,a.zzypbda_jskh,b.lswldw_dwmc,a.zzypbda_fwkh,
d.lswldw_dwmc,a.zzypbda_fcrq,a.zzypbda_yhsrq,a.zzypbda_zhczr,a.zzypbda_yj,a.zzypbda_syf,a.zzypbda_sl
from zzypbda as a
left join lswldw as b on b.lswldw_wldwbh=a.zzypbda_jskh
left join warehouses as c on c.warehouseid=a.zzypbda_szck 
left join lswldw as d on d.lswldw_wldwbh=a.zzypbda_fwkh
insert into #temp_fxyxpbxxcxb(pbbh,zt,szckid,szck,jskhbh,jskhmc,fwkhbh,fwkhmc,fcrq,hsrq,zhczr,yj,syf,sl)
select '合计' pbbh,'' zt,'' szckid,'' szck,'' jskhbh,'' jskhmc,'' fwkhbh,'' fwkhmc,'' fcrq,'' hsrq,'' zhczr,sum(yj),sum(syf),sum(sl) --  这里decimal类型怎么给空值啊，因为不会给空值所以就求和了。汗。
from #temp_fxyxpbxxcxb
select pbbh,zt,szckid,szck,jskhbh,jskhmc,fwkhbh,fwkhmc,fcrq,hsrq,zhczr,yj,syf,sl from #temp_fxyxpbxxcxb
drop table  #temp_fxyxpbxxcxb
end

Answer 1

语句没问题，你看你传入的参数对不对吧
insert into #temp_fxyxpbxxcxb(pbbh,zt,szckid,szck,jskhbh,jskhmc,fwkhbh,fwkhmc,fcrq,hsrq,zhczr,yj,syf,sl)
select '合计' pbbh,'' zt,'' szckid,'' szck,'' jskhbh,'' jskhmc,'' fwkhbh,'' fwkhmc,'' fcrq,'' hsrq,'' zhczr,null,null,null -- 这里decimal类型怎么给空值啊，因为不会给空值所以就求和了。汗。
from #temp_fxyxpbxxcxb

Answer 2

嗯 就是不知道decimal类型怎么给空值  想给yj，syf 这两个字段赋空值

Answer 3

不知道对不对
insert into #temp_fxyxpbxxcxb(pbbh,zt,szckid,szck,jskhbh,jskhmc,fwkhbh,fwkhmc,fcrq,hsrq,zhczr,yj,syf,sl)
select a.zzypbda_bh,a.zzypbda_ztbz,c.warehouseid,c.warehousename,a.zzypbda_jskh,b.lswldw_dwmc,a.zzypbda_fwkh,
d.lswldw_dwmc,a.zzypbda_fcrq,a.zzypbda_yhsrq,a.zzypbda_zhczr,a.zzypbda_yj,a.zzypbda_syf,a.zzypbda_sl
from zzypbda as a
left join lswldw as b on b.lswldw_wldwbh=a.zzypbda_jskh
left join warehouses as c on c.warehouseid=a.zzypbda_szck  
left join lswldw as d on d.lswldw_wldwbh=a.zzypbda_fwkh
where 
(a.zzypbda_bh=@pbbh 
or (isnull(@pbbh,'')='' 
and (c.warehouseid=@szckid 
or (isnull(@szckid,'')='' 
and(b.lswldw_wldwbh=@jskhbh
or(isnull(@jskhbh,'')='' 
and(d.lswldw_wldwbh=@fwkhbh
or(isnull(@fwkhbh,'')='' 
and (a.zzypbda_ztbz=@zt
or(isnull(@zt,'')='' 
and (a.zzypbda_fcrq<=@fcrq
or(isnull(@fcrq,'')='' 
and (a.zzypbda_ztbz='1' and a.zzypbda_yhsrq<=@hsrq
or(isnull(@hsrq,'')='' )
)
)
)
)
)
)
)
)
)
)
)
)

Answer 4

这个是对的，我的变量好像条件给错了，在界面上传值，那些变量是可以不填写，我赋空值和变量为空出来的结果不一样，有点乱......   现在的意思是，如果界面上不输入那些变量，就相当于没有那些条件了，应该是这样的，select a.zzypbda_bh,a.zzypbda_ztbz,c.warehouseid,c.warehousename,a.zzypbda_jskh,b.lswldw_dwmc,a.zzypbda_fwkh,
d.lswldw_dwmc,a.zzypbda_fcrq,a.zzypbda_yhsrq,a.zzypbda_zhczr,a.zzypbda_yj,a.zzypbda_syf,a.zzypbda_sl
from zzypbda as a
left join lswldw as b on b.lswldw_wldwbh=a.zzypbda_jskh
left join warehouses as c on c.warehouseid=a.zzypbda_szck  
left join lswldw as d on d.lswldw_wldwbh=a.zzypbda_fwkh那么在语句中怎么修改呢？

Answer 5

或者你应该这样
select a.zzypbda_bh,a.zzypbda_ztbz,c.warehouseid,c.warehousename,a.zzypbda_jskh,b.lswldw_dwmc,a.zzypbda_fwkh,
d.lswldw_dwmc,a.zzypbda_fcrq,a.zzypbda_yhsrq,a.zzypbda_zhczr,a.zzypbda_yj,a.zzypbda_syf,a.zzypbda_sl
from zzypbda as a
left join lswldw as b on b.lswldw_wldwbh=a.zzypbda_jskh
left join warehouses as c on c.warehouseid=a.zzypbda_szck  
left join lswldw as d on d.lswldw_wldwbh=a.zzypbda_fwkh
where (a.zzypbda_bh=@pbbh or isnull(@pbbh,'')='') 
and (c.warehouseid=@szckid or isnull(@szckid,'')='')
and (b.lswldw_wldwbh=@jskhbh or isnull(@jskhbh,'')='') 
and(d.lswldw_wldwbh=@fwkhbh or isnull(@fwkhbh,'')='' )
and (a.zzypbda_ztbz=@zt or isnull(@zt,'')='' )
and (a.zzypbda_fcrq<=@fcrq or isnull(@fcrq,'')='') 
and ((a.zzypbda_ztbz='1' and a.zzypbda_yhsrq<=@hsrq )or isnull(@hsrq,'')='' )

Answer 6

这个是正确的，但是现在的意思是，如果界面上不输入那些变量，就相当于没有那些条件了，应该是这样的，select a.zzypbda_bh,a.zzypbda_ztbz,c.warehouseid,c.warehousename,a.zzypbda_jskh,b.lswldw_dwmc,a.zzypbda_fwkh,d.lswldw_dwmc,a.zzypbda_fcrq,a.zzypbda_yhsrq,a.zzypbda_zhczr,a.zzypbda_yj,a.zzypbda_syf,a.zzypbda_sl from zzypbda as a
left join lswldw as b on b.lswldw_wldwbh=a.zzypbda_jskh
left join warehouses as c on c.warehouseid=a.zzypbda_szck   
left join lswldw as d on d.lswldw_wldwbh=a.zzypbda_fwkh你写的这个是变量为空值，这段查询后面有条件，各个变量为空值，这样查出来的结果是不一样的。