
CREATE PROCEDURE dbo.DeleteConfirmation
  @ReferenceNumber int, 
  @GuestNumber int, 
  @BatchesOnly int 
AS
/* Created May 2012 by Villy Damsbo */

DECLARE
@thisAttachment INT

DECLARE #LocalCursor CURSOR LOCAL FAST_FORWARD FOR

  SELECT AttachmentNumber 
  FROM ReservationAttachmentXTab WITH (NOLOCK)
  WHERE 
    ReferenceNumber = @ReferenceNumber AND
    GuestNumber = @GuestNumber AND
    AttachmentNumber IN (SELECT AttachmentNumber 
                         FROM Attachment WITH (NOLOCK)
                         WHERE Batch >= @BatchesOnly)                    
                            
OPEN #LocalCursor

FETCH NEXT FROM #LocalCursor
INTO @thisAttachment 

WHILE @@FETCH_STATUS = 0
BEGIN

  DELETE FROM ReservationAttachmentXTab 
  WHERE AttachmentNumber = @thisAttachment 
   
  DELETE FROM Attachment  
  WHERE AttachmentNumber = @thisAttachment
                           
  FETCH NEXT FROM #LocalCursor
  INTO @thisAttachment 
  
END

CLOSE #LocalCursor
DEALLOCATE #LocalCursor



