Select rows and Update same rows for locking?

Question

I need to write a procedure that will allow me to select x amount of rows and at the same time update those rows so the calling application will know those records are locked and in use. I have a column in the table named "locked". The next time the procedure is called it will only pull the next x amount of records that do not have the "locked" column checked. I have read a little about the OUTPUT method for SQL server, but not sure that is what I want to do.

Answer 1

Vote for Cade Roux's answer, using OUTPUT:

UPDATE #tbl
   SET locked = 1
OUTPUT INSERTED.*
 WHERE id IN (SELECT TOP 1 id
                FROM #tbl
               WHERE locked = 0
            ORDER BY id)​

Previously:

---

This is one of the few times I can think of using a temp table:

ALTER PROCEDURE temp_table_test
AS
BEGIN

   SELECT TOP 5000 * 
     INTO #temp_test
     FROM your_table
    WHERE locked != 1
 ORDER BY ?

   UPDATE your_table
      SET locked = 1
    WHERE id IN (SELECT id FROM #temp_test)

   SELECT *
     FROM #temp_test

   IF EXISTS (SELECT NULL
                FROM tempdb.dbo.sysobjects
               WHERE ID = OBJECT_ID(N'tempdb..#temp_test'))
   BEGIN
     DROP TABLE #temp_test
   END

END

This:

1. Fetches the rows you want, stuffs them into a local temp table
2. Uses the temp table to update the rows to be "locked"
3. SELECTs from the temp table to give you your resultset output
4. Drops the temp table because they live for the session

Answer 2

As you suggested, you can use the OUTPUT clause effectively:

Live demo: http://odata.stackexchange.com/stackoverflow/q/8068/so3319842

UPDATE #tbl
SET locked = 1
OUTPUT INSERTED.*
WHERE id IN (
    SELECT TOP 1 id
    FROM #tbl
    WHERE locked = 0
    ORDER BY id
)​

Also see this article:

http://www.sqlmag.com/article/tsql3/more-top-troubles-using-top-with-insert-update-and-delete.aspx