存储过程条件问题
create proc GetRoomTps
(
@parent char(10), --城市
@Node varchar(15),--区域
@StatDate varchar(20),--入住时间
@EndDate varchar(20),--结束时间
@Hote char(10), --酒店编号
@roomtypeid char(2) --房间型号
)
as
Declare @intEqId varchar(200)
if @Node <> ''
begin
select @intEqId=' and node ='+@Node+''
end
if @Hote <> ''
begin
select @intEqId=@intEqId+ ' and hotelist.HJHotelID='+@Hote
end
if @roomtypeid <> ''
begin
select @intEqId=@intEqId+ ' and roomTYpe.roomtypeid='+@roomtypeid
end
select *from ZxCms_HoteContent as hotec,ZXCms_city as zxct, huijinHotelList as hotelist
,( select HJHotelID,roomtypeid,Rstate,ZYprice,count(roomtypeid) as 'str' from huijinRoomTypeZY
where Rstate='空' and Zydate between '2013-08-15' and '2013-08-17'
group by HJHotelID,roomtypeid,Rstate,ZYprice
having count(roomtypeid)>=2
UNION ALL
select HJHotelID,roomtypeid,Rstate,ZYprice, count(roomtypeid) as 'str' from huijinRoomTypeZY
where Rstate='满' and Zydate between '2013-08-15' and '2013-08-17'
group by HJHotelID,roomtypeid,Rstate,ZYprice) as roomzy
,huijinRoomType as roomTYpe
where
hotec.Hote_cityID=zxct.node
and hotec.Hote_ID=hotelist.HJHotelID and
hotec.Hote_ID=roomzy.hjhotelid and
roomzy.hjhotelid=roomTYpe.hjhotelid and
roomzy.roomtypeid=roomTYpe.roomtypeid and
zxct.parent='001'+@intEqId
go
exec getroomtps '001','001001','1','1',1,'1'
调用没数据。。。
当参数不为空的时候有条件 比如：and hotelist.HJHotelID=1。
[解决办法]
create proc GetRoomTps
(@parent char(10), --城市
@Node varchar(15),--区域
@StatDate varchar(20),--入住时间
@EndDate varchar(20),--结束时间
@Hote char(10), --酒店编号
@roomtypeid char(2) --房间型号
)
as
select *from ZxCms_HoteContent as hotec,ZXCms_city as zxct, huijinHotelList as hotelist
,( select HJHotelID,roomtypeid,Rstate,ZYprice,count(roomtypeid) as 'str'
from huijinRoomTypeZY
where Rstate='空' and Zydate between '2013-08-15' and '2013-08-17'
group by HJHotelID,roomtypeid,Rstate,ZYprice
having count(roomtypeid)>=2
UNION ALL
select HJHotelID,roomtypeid,Rstate,ZYprice, count(roomtypeid) as 'str' from huijinRoomTypeZY
where Rstate='满' and Zydate between '2013-08-15' and '2013-08-17'
group by HJHotelID,roomtypeid,Rstate,ZYprice
)as roomzy
,huijinRoomType as roomTYpe
where hotec.Hote_cityID=zxct.node
and hotec.Hote_ID=hotelist.HJHotelID and
hotec.Hote_ID=roomzy.hjhotelid and
roomzy.hjhotelid=roomTYpe.hjhotelid and
roomzy.roomtypeid=roomTYpe.roomtypeid and
zxct.parent='001'
and node=case when @Node<>'' then @Node else node end
and hotelist.HJHotelID=case when @Hote<>'' then @Hote else hotelist.HJHotelID end
and roomTYpe.roomtypeid=case when @roomtypeid<>'' then @roomtypeid else roomTYpe.roomtypeid end
go
exec getroomtps '001','001001','1','1',1,'1'
