在VS2008开发报表项目遇到的语句问题

[szlixiaolong]

使用事件探查器跟踪出来的调用存储过程完整语句：
pr_jxc_daysum_item'2014-05-26','2014-06-24','1','0001','','','0'

pr_jxc_daysum_item是指：进销存日报表
pr_jxc_daysum_item 开始日期,结束日期,'1',4位门店编号,供应商编号,商品编号,'0'
如果门店编号，供应商编号，商品编号为空，就查询相应的数据。
@a,@b,@shop，指的是临时的报表参数，以下语句执行后，查询不到任何数据。。

exec pr_jxc_daysum_item
@a,@b,'1',@shop,'','','0'

select distinct CONVERT(VARCHAR, a.oper_date, 120) as 日期, a.init_qty as 期初数量, a.init_amt as 期初金额, a.settle_qty as 结存数量,
a.settle_amt as 结存金额 , a.pi_qty - a.ro_qty + a.gi_qty as 采购数量, a.pi_amt - a.ro_costamt as 采购成本,
a.io_in_qty as 调拨入库数量, a.io_in_amt as 调拨入库金额, a.cs_in_qty as 组装入库数量, a.cs_in_amt as 组装入库金额, a.sc_in_qty as 拆分入库数量 , a.sc_in_amt as 拆分入库金额, a.mi_qty as 配入数量,
a.mi_amt as 配入金额, a.oo_in_qty as 其他出入库数量, a.oo_in_amt as 其他出入库金额, a.pd_in_qty as 盘盈数量, a.pd_in_amt as 盘盈金额,
case when a.chg_qty>0 then a.chg_qty else 0 end as 货商转出数量,
case when a.chg_amt>0 then a.chg_amt else 0 end as 货商转出金额, case when a.tc_costamt>0 then a.tc_costamt else 0 end as 成本变更增加,
a.io_out_qty as 调拨出库数量, a.io_out_amt as 调拨出库金额, a.cs_out_qty as 组装出库数量 , a.cs_out_amt as 组装出库金额,
a.sc_out_qty as 拆分出库数量, a.sc_out_amt as 拆分出库金额, a.mo_qty as 配出数量, a.mo_amt as 配出金额, a.mo_costamt as 配出成本,
a.pos_qty - a.pos_ret_qty + a.pos_give_qty as 前台实销数量, a.pos_costamt - a.pos_ret_costamt + a.pos_give_costamt as 前台实销成本,
a.so_qty - a.ri_qty as 批发销售数量, a.so_costamt - a.ri_costamt as 批发销售成本, a.oo_out_qty as 其他出库数量, a.oo_out_costamt as 其他出库成本, a.pd_out_qty as 盘亏数量,
a.pd_out_amt as 盘亏金额, case when a.chg_qty<0 then -a.chg_qty else 0 end as 货商转出数量, case when a.chg_amt<0 then -a.chg_amt else 0 end as 货商转出金额,
case when a.tc_costamt<0 then -a.tc_costamt else 0 end as 成本变更减少, a.pi_amt - a.ro_amt as 采购金额, a.pos_amt - a.pos_ret_amt as 前台实销金额,
a.so_amt - a.ri_amt as 批发销售金额, a.oo_out_amt, a.zp_in_qty as 直配入库数量, a.zp_in_amt as 直配入库金额, a.zp_in_costamt as 直配入库成本 , a.zp_out_qty as 直配出库数量, a.zp_out_amt as 直配出库金额,
a.zp_out_costamt as 直配出库成本, a.tq_qty as 退仓数量, a.tq_amt as 退仓金额, a.tq_costamt as 退仓成本, a.tb_qty as 残次品退仓数量, a.tb_amt as 残次品退仓金额, a.tb_costamt as 残次品退仓成本,
d.item_subno aS 货号,
d.item_barcode as 条码, d.item_name as 商品名称, d.item_clsno as 类别,
d.item_unit_no as 单位, d.item_size as 规格

from ic_t_sup_daysum_tmp a
left join ic_t_branch_stock_target f on a.item_no = f.item_no and a.branch_no=f.branch_no,
view_item_info d,
happy_branch_area x,
happy_area_flag y
where x.branch_no=(case
when @shop='00' and a.branch_no like '00%' then left(a.branch_no, 2)
when @shop like '000%' and a.branch_no like '000%' then a.branch_no
else a.branch_no end)
and x.area_no=y.area_no and a.item_no = d.item_no and (d.item_display_flag<>'0' or '1'='1') and
( not ( pi_qty = 0 and pi_amt = 0 and
io_in_qty = 0 and io_in_amt = 0 and io_out_qty = 0 and io_out_amt = 0 and
cs_in_qty = 0 and cs_in_amt = 0 and cs_out_qty = 0 and cs_out_amt = 0 and
sc_in_qty = 0 and sc_in_amt = 0 and sc_out_qty = 0 and sc_out_amt = 0 and
mi_qty = 0 and mi_amt = 0 and mo_qty = 0 and mo_amt = 0 and
pos_qty = 0 and pos_costamt = 0 and pos_amt = 0 and pos_ret_qty = 0 and
pos_ret_costamt = 0 and pos_ret_amt = 0 and pos_give_qty = 0 and pos_give_costamt = 0 and
so_qty = 0 and so_costamt = 0 and so_amt = 0 and gi_qty = 0 and ro_qty = 0 and
ro_costamt = 0 and ro_amt = 0 and ri_qty = 0 and ri_costamt = 0 and ri_amt = 0 and
oo_in_qty = 0 and oo_in_amt = 0 and oo_out_qty = 0 and oo_out_costamt = 0 and oo_out_amt = 0 and
pd_in_qty = 0 and pd_in_amt = 0 and pd_out_qty = 0 and pd_out_amt = 0 and tc_costamt = 0 and chg_qty = 0 and chg_amt = 0 and
zp_in_qty = 0 and zp_in_amt = 0 and zp_in_costamt = 0 and zp_out_qty = 0 and zp_out_amt = 0 and zp_out_costamt = 0 and
tq_qty = 0 and tq_amt = 0 and tq_costamt = 0 and tb_qty = 0 and tb_amt = 0 and tb_costamt = 0)
or '0'='0' )


And y.area_no like (case when @area='*' then '%' else @area end )

And a.branch_no like (case
when @shop='' then '%%'
when @shop='00' and a.branch_no like '00%' then left(@shop, 2)+'%'
when @shop like '000%' and a.branch_no like '000%' then left(@shop, 2)+'%'
else @shop end )

And (d.item_no in (select item_no
from view_item_subno
WHERE item_subno LIKE @productno
or item_barcode LIKE @productno
or barcode LIKE @productno) or
isnull(@productno, 'all') = 'all')
AND ( CONVERT(VARCHAR, a.oper_date, 120) >= @a + '00:00:00' )
AND ( CONVERT(VARCHAR, a.oper_date, 120) <= @b + '23:59:59' )
group by
a.oper_date,a.init_qty, a.init_amt , a.settle_qty ,a.settle_amt,
a.pi_qty ,a.ro_qty ,a.gi_qty,a.pi_amt,a.ro_costamt,a.io_in_qty,a.io_in_amt , a.cs_in_qty , a.cs_in_amt, a.sc_in_qty , a.sc_in_amt, a.mi_qty,
a.mi_amt , a.oo_in_qty, a.oo_in_amt , a.pd_in_qty , a.pd_in_amt ,
a.chg_qty,
a.chg_amt, a.tc_costamt,
a.io_out_qty , a.io_out_amt , a.cs_out_qty , a.cs_out_amt,
a.sc_out_qty , a.sc_out_amt, a.mo_qty, a.mo_amt, a.mo_costamt,
a.oo_out_qty, a.oo_out_costamt , a.pd_out_qty ,
a.pd_out_amt,
a.oo_out_amt, a.zp_in_qty , a.zp_in_amt , a.zp_in_costamt , a.zp_out_qty, a.zp_out_amt,
a.zp_out_costamt, a.tq_qty , a.tq_amt , a.tq_costamt, a.tb_qty , a.tb_amt , a.tb_costamt , a.pos_qty,a.pos_ret_qty,a.pos_give_qty,a.pos_costamt , a.pos_ret_costamt , a.pos_give_costamt ,a.so_qty, a.ri_qty , a.so_costamt , a.ri_costamt ,a.ro_amt,a.pos_amt ,a.pos_ret_amt,a.so_amt , a.ri_amt,
d.item_subno,
d.item_barcode,
d.item_base_price2,
d.item_base_price3,
d.item_base_price4,
d.item_base_price5,
d.item_vip_price2,
d.item_vip_price3,
d.item_abc,
d.item_valid_day,
d.item_name,
d.item_clsno,
d.item_brand,
d.item_brandname,
d.item_unit_no,
d.item_size ,
d.item_price

order by d.item_subno

[szlixiaolong]

[xdashewan]

我们没有数据，为什么没有结果也不好说，只能教楼主一个分析方法，楼主可以一点点注释条件，但一个条件注释后获得数据，说明那条件把数据都排除了，然后去调查为什么会有这数据

[cadoupanpan]

是啊，SP写得时候不要急，一步一步慢慢来，这样知道哪里出错了，最大的可能是join, 或者是where上的问题。