存储过程条件问题
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),--结束时间