pro_bedlive_status.sql

一套宾馆管理系统

Create Procedure BedLive_Status
@room_no varchar(10)
as
declare @xx table
(
 room_id int not null,
 room_no varchar(10) not null,
 flooer_id smallint,
 bed_num smallint,
 live_no int ,
 roompro_id smallint not null,
 roompro_name varchar(30)
) 

insert into @xx 
 select a.room_id,a.room_no,a.flooer_id,a.bed_num,live_no,a.roompro_id,c.roompro_name
	from roomlist a left outer join 
	(select room_id ,count(*) Live_no from customerlist
	where bedsta_id=2 
	group by room_id) t1
	on a.room_id=t1.room_id
	left outer join RoomProperty c
	on a.roompro_id=c.roompro_id
	where a.room_no=@room_no 
update @xx set live_no=0 where live_no is null 
select * from @xx