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 (CHARINDEX(','+ convert(varchar(6),Number)+',', @Roomnumbers) > 0 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 
(CHARINDEX(','+ convert(varchar(6),Number)+',', @Roomnumbers) > 0 AND ((@DominoEnabled = 1 AND HotelNumber = @HotelNumber) OR (@DominoEnabled = 0)))

)) 

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 rooms (nolock) where (CHARINDEX(','+ convert(varchar(6),Number)+',', @Roomnumbers) > 0 AND ((@DominoEnabled = 1 AND HotelNumber = @HotelNumber) OR (@DominoEnabled = 0))))) -- remove allocations


delete from roomroomsubtypextab where 
roomnumber in (select roomnumber from rooms where (CHARINDEX(','+ convert(varchar(6),Number)+',', @Roomnumbers) > 0 AND ((@DominoEnabled = 1 AND HotelNumber = @HotelNumber) OR (@DominoEnabled = 0))))

if exists (select name from sysobjects (nolock) where name = 'WareRoomXTab')
  delete from WareRoomXTab where 
  FK_roomnumber in (select roomnumber from rooms where (CHARINDEX(','+ convert(varchar(6),Number)+',', @Roomnumbers) > 0 AND ((@DominoEnabled = 1 AND HotelNumber = @HotelNumber) OR (@DominoEnabled = 0))))

delete from RoomAllocationProfileXTab where 
roomnumber in (select roomnumber from rooms where (CHARINDEX(','+ convert(varchar(6),Number)+',', @Roomnumbers) > 0 AND ((@DominoEnabled = 1 AND HotelNumber = @HotelNumber) OR (@DominoEnabled = 0))))

delete from RoomMeterExt 
where FK_RoomNumber in (select roomnumber from rooms where (CHARINDEX(','+ convert(varchar(6),Number)+',', @Roomnumbers) > 0 AND ((@DominoEnabled = 1 AND HotelNumber = @HotelNumber) OR (@DominoEnabled = 0))))

delete from CleanStatusLog
where FK_RoomNumber in (select roomnumber from rooms where (CHARINDEX(','+ convert(varchar(6),Number)+',', @Roomnumbers) > 0 AND ((@DominoEnabled = 1 AND HotelNumber = @HotelNumber) OR (@DominoEnabled = 0))))

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

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

delete from rooms where -- delete the rooms
(CHARINDEX(','+ convert(varchar(6),Number)+',', @Roomnumbers) > 0 AND ((@DominoEnabled = 1 AND HotelNumber = @HotelNumber) OR (@DominoEnabled = 0)))

DROP TABLE #delRoom

