CREATE PROCEDURE DBO.DeleteRooms @RoomNumbers VARCHAR(1000), -- This is the visible room numbers in Picasso, not the key number in the database. E.g  1,2,5,8
                                 @HotelNumber INT,
                                 @DominoEnabled BIT AS

SELECT RoomNumber
INTO #delRoom
FROM Rooms WITH (NOLOCK) where (Number in (select number from DBO.iter_intlist_to_table_No_Dummy(@Roomnumbers)) AND ((@DominoEnabled = 1 AND HotelNumber = @HotelNumber) OR (@DominoEnabled = 0))) 

delete from reservationroomlinextab where referencenumber<1 and linenumber in 
(select linenumber from reservationroomline where roomnumber in 
(select roomnumber from rooms (nolock) 
WHERE RoomNumber IN (SELECT RoomNumber FROM #delRoom)
)) 

delete from reservationroomline where not linenumber 
in (select linenumber from reservationroomlinextab(nolock)) -- remove roomlines not linked to any reservation

update reservationroomline set roomnumber=null where linenumber in 
(select linenumber from reservationroomline where roomnumber IN (SELECT RoomNumber FROM #delRoom))


delete from roomroomsubtypextab where 
roomnumber IN (SELECT RoomNumber FROM #delRoom)

if exists (select name from sysobjects (nolock) where name = 'WareRoomXTab')
  delete from WareRoomXTab where 
  FK_roomnumber IN (SELECT RoomNumber FROM #delRoom)

delete from RoomAllocationProfileXTab where 
roomnumber IN (SELECT RoomNumber FROM #delRoom)

delete from RoomMeterExt 
where FK_RoomNumber IN (SELECT RoomNumber FROM #delRoom)

delete from CleanStatusLog
where FK_RoomNumber IN (SELECT RoomNumber FROM #delRoom)

delete FollowupRoomsXtab
where FK_RoomNumber in (SELECT RoomNumber FROM #delRoom)

DELETE RoomsFacilityItemXTab
WHERE FK_RoomNumber IN (SELECT RoomNumber FROM #delRoom)

DELETE RoomCleaningDate
WHERE RoomNumber IN (SELECT RoomNumber FROM #delRoom)

delete from rooms where -- delete the rooms
RoomNumber IN (SELECT RoomNumber FROM #delRoom)

DROP TABLE #delRoom

