手写版
1 if exists(select * from sysobjects where name ='usp_Seachroom') 2 drop procedure usp_Seachroom 3 go 4 create proc usp_seachroom 5 @roomstate int=-1, 6 @roomtype int=-1, 7 @roomnum nvarchar(50)=null, 8 @bedcount int=-1 9 as 10 declare @sql nvarchar(500)= 11 'select RoomTypeId,RoomStateId,Bedcount, RoomNum,roomstate.statename , roomtype.typename, Description from room 12 inner join roomstate on room.roomstateid=roomstate.stateid 13 inner join roomtype on room.roomtypeid=roomtype.typeid' 14 if(@roomstate<>-1) 15 set @sql+=' and roomstate.stateid=@rstate' 16 if(@roomtype<>-1) 17 set @sql+=' and roomtype.typeid=@rtype' 18 if(@roomnum is not null) 19 set @sql+=' and room.roomnum=@rnum' 20 if(@bedcount<>-1) 21 set @sql+=' and room.bedcount=@bcount' 22 23 exec sp_executesql @sql,N'@rstate int,@rtype int,@rnum nvarchar(50),@bcount int',@rstate=@roomstate,@rtype=@roomtype,@rnum 24 =@roomtype,@bcount=@bedcount 25 go 26 27 exec usp_seachroom @bedcount=2
1 if exists(select * from sysobjects where name ='usp_LeaveRoom') 2 drop procedure usp_LeaveRoom 3 go 4 --1 计算金额 5 --普通房间 6 --时钟房间 7 --2 天数 8 --如果退房日期和开放日期不是同一天，算到中午12点 9 --如果是同一天开放和退房，算一天 10 --3修改房间状态 11 --4在客户端显示详细退房信息 12 create procedure usp_Leaveroom 13 @gusetid int,@totalmoney int out 14 as 15 declare @price money 16 declare @roomtype nvarchar(50) 17 declare @roomnum nvarchar(50) 18 declare @intime datetime 19 --declare @totalmoney money 20 select @roomnum=guest.RoomNum,@price=Price,@roomtype=TypeName,@intime=InTime from guest 21 inner join Room on 22 guest.RoomNum=room.RoomNum 23 inner join RoomType on 24 RoomType.TypeId=room.RoomTypeId 25 where guest.GuestId=@gusetid 26 27 declare @day int 28 --算时间 29 if @roomtype='时钟房' 30 begin 31 set @day=DATEDIFF(hh,@intime,GETDATE())+ceiling((DATEPART(MI,GETDATE())*1.0/60)) 32 if(@day<=3) 33 begin 34 set @totalmoney=@price 35 end 36 else 37 begin 38 set @totalmoney=@price+(@day-3)*30 39 end 40 end 41 else 42 begin 43 set @day= DATEDIFF(DD,@intime,GETDATE()) 44 if (@day=0 or (DATEPART(HH,GETDATE())>12)) 45 begin 46 set @day+=1 47 end 48 set @totalmoney=@day*@price 49 end 50 declare @error int 51 begin transaction 52 update Room set RoomStateId=(select StateId from RoomState where StateName='待租') where RoomNum= 53 @roomnum 54 set @error+=@@ERROR 55 update guest set OutTime =GETDATE(),TotalMoney=@totalmoney where GuestId=@gusetid 56 set @error+=@@ERROR 57 if @error<>0 58 begin 59 rollback transaction 60 return -1 61 end 62 else 63 begin 64 commit transaction 65 return 1 66 end 67 go 68 69 declare @totalmoney int 70 exec usp_Leaveroom 6,@totalmoney out 71 print @totalmoney
1 if exists(select * from sysobjects where name='usp_guestinsert') 2 drop proc usp_guestinsert 3 go 4 create proc usp_guestinsert 5 @Name nvarchar(50), 6 @Gender nchar(1), 7 @CardId char(18), 8 @Phone char(11)=null, 9 @RoomNum nvarchar(50), 10 -- @InTime datetime, 11 @Deposit money, 12 @OutTime datetime=null, 13 @TotalMoney money=null 14 15 as 16 declare @error int=0 17 begin transaction 18 19 insert into guest values(@Name,@Gender,@CardId,@Phone,@RoomNum,default,@Deposit,@OutTime,@TotalMoney) 20 set @error+=@@ERROR 21 update Room set RoomStateId=(select StateName from roomstate where StateName='已租') where RoomNum=@RoomNum 22 set @error+=@@ERROR 23 if(@error<>0) 24 begin 25 rollback transaction 26 return -1 27 end 28 else 29 begin 30 commit transaction 31 return 1 32 end 33 go
带注释版
查询客房存储过程
1 if exists(select * from sysobjects where name='usp_SearchRoom') 2 drop proc usp_SearchRoom 3 go 4 create proc usp_SearchRoom 5 @roomnum nvarchar(50)=null,--客房号 6 @stateId int=0,--状态ID 7 @typeName nvarchar(50)=null,--类型名称 8 @bednum int=-1 --床位数 9 as 10 --初始的查询命令 11 declare @sql Nvarchar(500)='SELECT dbo.Room.RoomNum, dbo.Room.RoomStateId, dbo.Room.RoomTypeId,dbo.Room.BedNum, dbo.Room.Description, dbo.RoomState.StateName, dbo.RoomType.TypeName, dbo.RoomType.Price, dbo.RoomType.Description AS RoomDes FROM dbo.Room INNER JOIN dbo.RoomState ON dbo.Room.RoomStateId = dbo.RoomState.StateId INNER JOIN dbo.RoomType ON dbo.Room.RoomTypeId = dbo.RoomType.TypeId' 12 if(@roomnum is not null) 13 set @sql=@sql+' and room.roomnum=@rnum' 14 if(@stateId<>0) 15 set @sql=@sql+' and StateId=@sId' 16 if(@typeName is not null) 17 set @sql=@sql+' and TypeName=@tName' 18 if(@bednum<>-1) 19 set @sql=@sql+' and BedNum=@bnum' 20 --在sql中执行sql命令 21 --exec sp_executesql N'select * from student where classid=@id',N'@id int',@id=7 22 --如果 是将参数分开创建，那么在调用的时候必须赋值所有参数值 23 --exec sp_executesql @sql,N'@rnum nvarchar(50)',N'@sId int',@rnum=@roomnum,@sId=@stateId 24 --如果将所有参数在一起创建，那么在调用的时候可以任意指定其中的参数实参值 相当于in 25 exec sp_executesql @sql,N'@rnum nvarchar(50),@sId int,@tName nvarchar(50),@bnum int',@rnum=@roomnum,@sId=@stateId,@tName=@typeName,@bnum=@bednum--将存储过程接收的参数赋值给sql命令中的参数变量 26 go 27 28 exec usp_SearchRoom @typeName='单人间'
1 --1.传入房间号 2 --2.客户ID：修改时使用 3 4 ----计算金额： 5 --1.不同房间类型的价格不一样 6 -- 1.钟点房：三个小时以内，原价，超出部门每一小时30块 7 -- 2.其它房间按指定价格计算 8 9 --2.天数 10 -- 1.如果退房日期和开房日期不是同一天，算到中午12点 11 -- 2.如果是同一天开房日期和退房，那么就算做一天 12 13 --3.退房后修改房间的状态为 空闲 14 --4.在客户端显示详细退房信息 15 16 if exists(select * from sysobjects where name='usp_LeaveRoom') 17 drop proc usp_LeaveRoom 18 go 19 create proc usp_LeaveRoom 20 @totalMoney money output,--总金额 21 @GuestId int, --客户编号 22 @RoomNum nvarchar(50) --房间号 23 as 24 declare @price money --房间价格 25 declare @inTime datetime --入住日期 26 declare @roomtype nvarchar(50) --房间类型 27 --declare @totalMoney money --总金额 28 select @price=price,@inTime=intime ,@roomtype=TypeName 29 from guest 30 inner join Room on guest.RoomNum=Room.RoomNum 31 inner join RoomType on Room.RoomTypeId=RoomType.TypeId 32 where guest.GuestId=@GuestId 33 declare @days int --入住天数或者入住小时数 34 --判断房间的类型，因为不同类型计算价格的单位(天，小时)不一样 35 if (@roomtype='钟点房') --8:00 11:59 36 begin 37 set @days=(select DATEDIFF(hh,intime,getdate()) from guest where GuestId=@GuestId)+ceiling((select datepart(MI,getdate()) from guest where GuestId=@GuestId)*1.0/60) 38 if(@days<=3)--如果没有超时，不需要额外计算金额 39 set @totalMoney=@price; 40 else 41 set @totalMoney=@price+(@days-3)*30 --如果超过三个小时，多出的每一小时按30块计算 42 end 43 else 44 begin 45 set @days=(select DATEDIFF(dd,intime,getdate()) from guest where GuestId=@GuestId)--获取天数 46 --如果 是当天开房和退房，那么应该算为一天，如果 退房时间在下午12点之后，那么应该增加一天 47 if(@days=0 or (select DATEPART(hh,getdate()) from guest where GuestId=@GuestId)>12) 48 set @days+=1 49 set @totalMoney=@days*@price 50 end 51 declare @error int=0 --记录操作中可能出现的错误号，做为事务的判断标准 52 begin transaction 53 --记录当前客户退房详细信息 54 update guest set OutTime=GETDATE(),TotalMoney=@totalMoney where GuestId=@GuestId 55 set @error+=@@ERROR 56 --修改退房之后的房间状态 57 update Room set RoomStateId=(select StateId from RoomState where StateName='空闲') where RoomNum=@RoomNum 58 set @error+=@@ERROR 59 if @error<>0 --说明出错 60 begin 61 rollback transaction 62 return -1 63 end 64 else 65 begin 66 commit transaction 67 return 1 68 end 69 go 70 71 exec usp_LeaveRoom 4 ,'F2009'
1 if exists(select * from sysobjects where name='usp_insertGuest') 2 drop proc usp_insertGuest 3 go 4 create proc usp_insertGuest 5 @name nvarchar(50), 6 @Gender nchar(1), 7 @CardId char(18), 8 @Phone char(11)=null, 9 @RoomNum nvarchar(50), 10 @Deposit money, 11 @OutTime datetime=null, 12 @TotalMoney money=null 13 as 14 declare @error int =0--记录错误号 15 begin transaction 16 insert into guest values(@name,@Gender,@CardId,@Phone,@RoomNum,default,@Deposit,@OutTime,@TotalMoney) 17 set @error=@error+@@ERROR 18 --修改当前房间的状态为 已入住 19 update Room set RoomStateId=(select StateId from RoomState where StateName='已入住') where RoomNum=@RoomNum 20 set @error=@error+@@ERROR 21 if(@error<>0) 22 begin 23 rollback transaction 24 return -1 25 end 26 else 27 begin 28 commit transaction 29 return 1 30 end 31 go