CREATE PROCEDURE DBO.LinkAttachmentsToReservation 
  @CustomerNumber INT, @ReferenceNumber INT, @GuestNumber INT AS
SET NOCOUNT ON

/* Created by Villy Damsbo 1/7-2011 */

DECLARE @AttachmentNumber INT

DECLARE #LocalCursor CURSOR LOCAL FAST_FORWARD FOR

  SELECT CX.AttachmentNumber 
  FROM CustomerAttachmentXTab CX WITH (NOLOCK) 
  WHERE 
    CX.CustomerNumber = @CustomerNumber AND                      
    NOT EXISTS (SELECT * 
                FROM ReservationAttachmentXTab RX WITH (NOLOCK) 
                WHERE 
                  RX.ReferenceNumber = @ReferenceNumber AND 
                  RX.GuestNumber = @GuestNumber AND
                  RX.AttachmentNumber = CX.AttachmentNumber)                         
                            
OPEN #LocalCursor

FETCH NEXT FROM #LocalCursor
INTO @AttachmentNumber

WHILE @@FETCH_STATUS = 0
BEGIN

  INSERT INTO ReservationAttachmentXTab
   (ReferenceNumber, GuestNumber, AttachmentNumber) 
  VALUES
   (@ReferenceNumber, @GuestNumber, @AttachmentNumber)
   
  FETCH NEXT FROM #LocalCursor
  INTO @AttachmentNumber
  
END

CLOSE #LocalCursor
DEALLOCATE #LocalCursor






