SQL 第八章

--阶段1:根据输入的客房类型名称,统计入住的客人总人数  
create procedure usp_GetGuestNum   
@TypeName varchar(32),  
@GuestNum int output  
as  
  
select @GuestNum=COUNT(*) from GuestRecord  
where RoomID in  
(  
  select RoomID from Room  
  where RoomTypeID in  
  (  
    select RoomTypeID from RoomType  
    where TypeName=@TypeName  
  )  
)  
--调用存储过程  
declare @num int  
exec usp_GetGuestNum '张淼',@num output  
print 'total:'+convert(nvarchar(20),@num)   
  
  
--阶段2:根据房间号查询客房信息  
--通过房间号查询客房的相关信息  
--如果房间号为-1，表示查询所有客房信息，包括客房ID，床位数，  
--客房状态ID,客房类型和房价。  
create procedure usp_RoomInfo  
 @roomnum int  
as   
if(@roomnum=-1)  
 begin  
    
   select room.RoomID,Room.BedNum,Room.RoomStateID,TypeName,TypePrice  
    from room,roomtype  
    where room.roomtypeid=Roomtype.TypeID  
 end  
  
else  
begin  
    select room.RoomID,Room.BedNum,Room.RoomStateID,TypeName,TypePrice