

CREATE PROCEDURE DBO.DeleteOutOfOrderAndOutForService @RoomNumber INT, @DeletedRows INT OUTPUT AS
SET NOCOUNT ON
/*
    Created by Jacob Pedersen March 2006

    Last Changed:
    N/A

    Notes:
    - Used when deleting Rooms from HotelSetup
*/

SET @DeletedRows = 0

DECLARE @LineNumber INT

DECLARE #CleanUpCur CURSOR FORWARD_ONLY FOR

  SELECT RL.LineNumber FROM ReservationRoomLine RL WITH (NOLOCK)
  INNER JOIN ReservationRoomLineXTab RLX WITH (NOLOCK) ON RL.RoomNumber=@RoomNumber AND RL.LineNumber=RLX.LineNumber AND RLX.ReferenceNumber<=0

OPEN #CleanUpCur

FETCH NEXT FROM #CleanUpCur
INTO @LineNumber

WHILE (@@FETCH_STATUS <> -1)
BEGIN
  DELETE FROM ReservationRoomLineXTab WHERE LineNumber=@LineNumber

  DELETE FROM ReservationRoomLine WHERE LineNumber=@LineNumber
  SET @DeletedRows = @DeletedRows + 1

  FETCH NEXT FROM #CleanUpCur
  INTO @LineNumber
END

CLOSE #CleanUpCur
DEALLOCATE #CleanUpCur

