pro_listbedstatus_new1.29.sql

一套宾馆管理系统

Create Procedure ListBedStatus
  @bedno varchar(10)
  AS
	DECLARE @i INT
	DECLARE @yy SMALLINT
	DECLARE @xx TABLE
	  (
	   bed_no SMALLINT not null identity(1,1),
	   bed_status SMALLINT 
	   )
	SET @i=1
	SELECT @yy=bed_num FROM roomList WHERE room_no=@bedno
	WHILE @i<=@yy
	  BEGIN
	   INSERT INTO @xx(bed_status) VALUES(0)
	   SET @i=@i+1
	  END 
	UPDATE @xx SET bed_status=1 
	  WHERE bed_no in (SELECT bed_no FROM customerlist a LEFT
	                   outer join RoomList b On a.room_id=b.room_id 
	                   WHERE a.bedSta_id=2 and b.room_no=@bedno) 
	SELECT * from @xx