title: "Pop" Record -- Select And Delete In The Same Statement (SQL Server 2005)
body: <p>I have a C# application that is used by about a dozen employees simultaneously.  It simply SELECTs the top record from a SQL Server 2005 table, DELETE it, then displays the data on a form.<br>
  As soon as the record is selected it needs to be deleted so 2 ppl don't grab and work on the same record.<br>
  Fairly Straight Forward...  </p>
  
  <p>I found a suggestion a while ago (cant find the site i got it from, sorry) to do a SELECT and DELETE in the same statement and execute a <strong>SqlCommand.ExecuteReader()</strong> with this "compound" statement:</p>
  
  <pre><code>SELECT TOP 1 * 
  FROM Call_Table 
  WHERE Call_Table.hold_call_till &lt;= GetDate() 
  ORDER BY Call_Table.attempts ASC, Call_Table.custno ASC;
  DELETE 
  FROM Call_Table
  WHERE Call_Table.custno = (SELECT TOP 1 Call_Table.custno 
                             FROM Call_Table 
                             WHERE Call_Table.hold_call_till &lt;= GetDate() 
                             ORDER BY Call_Table.attempts ASC, Call_Table.custno ASC);
  </code></pre>
  
  <p>Its been working quite well so far, but i feel like i've just been lucky.  We're hiring about 5 new ppl, and i would like to totally sure this will continue to work.  </p>
  
  <p>Im interested to hear the opinion of more seasoned vets in this area.</p>
  
  <p>Should I stick with the "If its not broken, dont fix it" approach?? Or should I step up my game and use some kind of record_locks or stored procs??</p>
  
  <p>Any suggestions will be openly accepted.  I can supply more info about the table or C# application if necessary.  </p>