一个令人头痛的存储过程
ALTER PROCEDURE dbo.sp_shopOrderSearch
@orderid varchar(30), -- 订单编号
@orderstate int, -- 订单状态
@giftname varchar(200),-- 礼品名称
@giftid varchar(30), -- 礼品编号
@order_giftpoint1 int, -- 订单礼品积分1
@order_giftpoint2 int, -- 订单礼品积分2
@mobilephone varchar(20), -- 用户
@changeddate1 varchar(20),-- 兑换日期/订单日期1
@changeddate2 varchar(20),-- 兑换日期/订单日期2
@busid varchar(30),-- 商家ID
@deliverymodo int, -- 配送方式
@deliveryarea varchar(50), -- 配送区域
@deliverModeName varchar(30), --配送单位
@receiveaddr varchar(255) -- 收货地点/领取地点
AS
DECLARE @returnvalue INT -- 返回值( 11 - 检索成功; 10 - 没有检索到数据)
BEGIN
SET NOCOUNT ON
begin
select a.id,a.orderid,a.mobilephone,a.giftid,a.giftname,a.busid,a.order_giftpoint,a.shopcartdate,a.ordernum,opinionid,
a.changeddate,a.smstype,a.seq,a.deliseq,deliverModeName,
case a.deliverymodo
when '10 ' then '自行领取 '
when '21 ' then '配送中心岛内配送 '
when '24 ' then '配送中心邮递快件 '
when '34 ' then '商家邮递快件 '
when '22 ' then '配送中心岛外配送 '
when '31 ' then '商家岛内配送 '
when '32 ' then '商家岛外配送 '
end as deliverymodo, a.orderstate, a.orderproclass
from dbo.shop_shopcarttb a
where (a.orderstate> 0 and a.orderstate <> 899 and a.orderstate <> 900)
and not (seq is null or seq = ' ') and not (deliseq is null or deliseq = ' ')
/** 根据前台页面所提供的条件检索 **/
-- 新旧订单
and a.orderproclass =
case
when (@orderstate is null or @orderstate= ' ' or @orderstate <=0) then a.orderproclass
when (@orderstate=99) then 0
else 1
end
-- 订单编号
and a.orderid =
case
when (@orderid= ' ' or @orderid is null) then a.orderid
when (@orderid is not null) then @orderid
end
-- 商家编号
and (a.busid =
case
when (@busid= ' ' or @busid is null) then a.busid
when (@busid is not null) then @busid
end
--配送单位
or a.deliverModeName=
case
when (@deliverModeName is not null) then @deliverModeName
end )
-- 订单礼品名称
and a.giftname like
case
when (@giftname= ' ' or @giftname is null) then a.giftname
when (@giftname is not null) then '% ' + @giftname + '% '
end
-- 订单礼品编号
and a.giftid =
case
when (@giftid= ' ' or @giftid is null) then a.giftid
when (@giftid is not null) then @giftid
end
-- 兑换用户
and a.mobilephone =
case
when (@mobilephone= ' ' or @mobilephone is null) then a.mobilephone
when (@mobilephone is not null) then @mobilephone
end
-- 订单状态
and a.orderstate =
case
when (@orderstate=0 or @orderstate is null) then a.orderstate
when (@orderstate is not null) then @orderstate
end
-- 配送方式
and a.deliverymodo =
case
when (@deliverymodo=0 or @deliverymodo is null) then a.deliverymodo
when (@deliverymodo> =1) then @deliverymodo
end
-- 订单积分
and a.order_giftpoint > =
case
when (@order_giftpoint1> =1) then @order_giftpoint1
when (@order_giftpoint1=0 or @order_giftpoint1 is null) then a.order_giftpoint
end
and a.order_giftpoint <=
case
when (@order_giftpoint2> =1) then @order_giftpoint2
when (@order_giftpoint2=0 or @order_giftpoint2 is null) then a.order_giftpoint
end
-- 兑换日期/订单日期
and (a.changeddate > =
case
when (@changeddate1 is null) or (@changeddate1= ' ') then a.changeddate
when (@changeddate1 is not null) then RTRIM(convert(char(20),@changeddate1,120))
end
and a.changeddate <=
case
when (@changeddate2= ' ' or @changeddate2 is null) then replace(RTRIM(convert(char(20),getdate(),120)), '/ ', '- ')
when (@changeddate2 is not null) then RTRIM(convert(char(20),@changeddate2,120))
end )
-- 排序
order by a.mobilephone asc, a.orderid asc,changeddate desc
if (@@ROWCOUNT> =1) set @returnvalue = 11 else set @returnvalue=10
RETURN @returnvalue
end
end
这个存储过程中加了
-- 订单礼品名称
and a.giftname like
case
when (@giftname= ' ' or @giftname is null) then a.giftname
when (@giftname is not null) then '% ' + @giftname + '% '
end
后,有点订单查不出来?应该怎么解决?
[解决办法]
改为:
and a.giftname like
case
when (@giftname= ' ' or @giftname is null) then a.giftname+ '% '
when (@giftname is not null) then '% ' + @giftname + '% '
end
[解决办法]
太长了,帮你ding!
[解决办法]
-- 订单礼品名称
and a.giftname like
case
when (@giftname= ' ' or @giftname is null) then a.giftname
when (@giftname is not null) then '% ' + @giftname + '% '
end
后,有点订单查不出来?应该怎么解决?
=======> 你多加条件了,当然查出记录就少了,你加的句子语法是没有问题的,问题出在你要表达的意思和你加的语句不相符,所以你应该说明你要添加什么条件,我们才好帮你判断你的语句是不是对的.
