title: Lock row for reading on SQL Server?
body: <p>In the bit of SQL down I would like to get and delete the next row of a table matching specific criteria. However, I want to prevent other services executing the same bit of SQL to return the same row. I was thinking about transactions or row locks but can't see how either one would help me.</p>
  
  <pre><code>DECLARE @tblTempRow TABLE(intUserID int, intBlobID int)
  
  -- Get the next match and remember in temp table. I want to prevent that other processes return the same row.
  INSERT INTO @tblTempRow(intUserID, intBlobID)
  SELECT TOP 1 intUserID, intBlobID FROM Schedule WHERE intScheduleType = @intScheduleType
  
  -- Delete if requested.
  IF(@intDeleteAfterGet = 1)
  BEGIN
         DELETE FROM
          Schedule
      WHERE
          intUserID = (SELECT intUserID FROM @tblTempRow)
          AND intBlobID = (SELECT intBlobID FROM @tblTempRow)
                  AND intScheduleType = @intScheduleType
  END
  
  -- Return the temp table.
  SELECT intUserID, @intScheduleType, intBlobID FROM @tblTempRow
  </code></pre>