CREATE PROCEDURE DBO.[GetTotalNumberOfRoomsSP]  (@referencenumber INT)  AS
SET NOCOUNT ON
/*
  DECLARE @rooms INT     SET @rooms     = 0
  DECLARE @lastline INT  SET @lastline  = 0
  DECLARE @noofrooms INT SET @noofrooms = 0
  DECLARE @line INT      SET @line      = 0
  
  DECLARE res_cur CURSOR FOR 
  SELECT reservationRoomline.Linenumber, numberofrooms
  FROM   RESERVATIONS (nolock) INNER JOIN
         RESERVATIONROOMLINEXTAB (nolock) ON RESERVATIONS.ReferenceNumber = RESERVATIONROOMLINEXTAB.ReferenceNumber AND RESERVATIONS.GuestNumber = RESERVATIONROOMLINEXTAB.GuestNumber INNER JOIN
         RESERVATIONROOMLINE (nolock) ON RESERVATIONROOMLINEXTAB.LINENUMBER = RESERVATIONROOMLINE.LINENUMBER
  WHERE (RESERVATIONS.ReferenceNumber = @referencenumber)
  OPEN res_cur

  FETCH NEXT FROM res_cur INTO @line, @noofrooms

  WHILE @@FETCH_STATUS = 0
  BEGIN
    IF @lastline <> @line 
      SET @rooms = @rooms + @noofrooms
    SET @lastline = @line
    FETCH NEXT FROM res_cur INTO @line, @noofrooms
  END
  CLOSE res_cur
  DEALLOCATE res_cur

  SELECT @rooms as Rooms
*/

SELECT SUM(NumberOfRooms) AS Rooms FROM ReservationRoomLine WITH (NOLOCK) 
WHERE LineNumber IN (
  SELECT DISTINCT LineNumber FROM ReservationRoomLineXTab WITH (NOLOCK) WHERE ReferenceNumber=@ReferenceNumber
)