
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE dbo.ClearAllotmentWhenChangingHotel @AllotmentNumber INT, @HotelNumber INT 
AS
BEGIN
    DECLARE @RoomTypeNumber INT
    DECLARE @Datestamp varchar(8)
    DECLARE allotCur cursor for
		SELECT RoomTypeNumber,DateStamp FROM allotmentlines(nolock) WHERE AllotmentNumber = @AllotmentNumber
		AND RoomTypeNumber IN (SELECT RoomTypeNumber FROM RoomTypes(nolock) WHERE HotelNumber <> @HotelNumber)
    
    OPEN AllotCur
    FETCH NEXT FROM AllotCur INTO @RoomTypeNumber,@DateStamp
    WHILE  (@@FETCH_STATUS = 0)
    BEGIN
      DELETE FROM AllotmentLines WHERE AllotmentNumber= @Allotmentnumber AND RoomTypeNumber = @RoomTypeNumber AND DateStamp = @Datestamp
      FETCH NEXT FROM AllotCur INTO @RoomTypeNumber,@DateStamp
    END
    CLOSE AllotCur
    DEALLOCATE AllotCur
END
GO
