title: Processing table data in Queue format
body: <p>The following is a main portion of the code from an SP that I am using to process table data in a FIFO queue order. The SP could be called by 100's of processes at the same time. </p>
  
  <pre><code>    create   TABLE #STAG(ident BIGINT)
  
      begin try
  
      begin tran
      insert into CaseLock (id,locktime,sessionid,lockid) output inserted.id into #STAG
      select top 1
           i.ident
          ,getutcdate()
          ,case when @sess is null then i.sessionid else @sess end
          ,newid()
        from QueueItem i WITH (ROWLOCK, READPAST)     where
          not exists(select 1 from CaseLock lck WITH (ROWLOCK, READPAST) where  lck.id = i.ident)
          and i.QUEUEIDENT    = @QUEUEIDENT       
          and i.finished is null and (i.deferred is null or i.deferred &lt; getutcdate())   
  
  
  
      commit
  
      end try
      begin catch
         rollback
      end catch
  
      Select i.encryptid, i.id, i.ident, i.keyvalue, i.data, i.status, i.attempt
      from QueueItem i  WITH (ROWLOCK, READPAST) 
      where EXISTS(SELECT 1 FROM #STAG WHERE [#STAG].[ident]=i.ident)
  </code></pre>
  
  <p>The data in the QueueItem table can not be deleted, we use the CaseLock table to lock the record that has been selected for processing. Once a record has been procesed the finished column get updated to current datetime.</p>
  
  <p>I am getting </p>
  
  <blockquote>
    <p>Violation of PRIMARY KEY constraint 'PK_CaseLock'. Cannot insert
    duplicate key in object 'dbo.CaseLock'.  The duplicate key value is
    (105).</p>
  </blockquote>
  
  <p>My problem is the SP must always return a data, the application will crash if no data is returned from the sp. </p>
  
  <p>How can I modify the code to resolve the Primary key violation error and it also returns a record?</p>