The code below the line is intended to find the next 'Queued' row in an SQL Server 2000 inbox table, change its status to 'Processing' and then return the primary key to a Visual FoxPro application.

I was a bit confused about the best technique to handle this task. Looks like I could change the SET ISOLATION LEVEL, but I am unsure how to set the LEVEL back (does that happen once the Transaction completes?).

Does the ROWLOCK hint only pertain to INSERTs (read that somewhere)? This code will be used in a high transaction environment and probably under a COM+ environment.

Any feedback on the correct way of handling this task will be appreciated.

===============

CREATE PROCEDURE GetInBoxRows AS
 
CREATE TABLE #QueuedInBoxRows (pk uniqueidentifier)
 
BEGIN TRANSACTION
 INSERT INTO #QueuedInBoxRows
 SELECT TOP 1 pk 
  FROM inbox WITH (HOLDLOCK, ROWLOCK)
  WHERE state = 'QUEUED'
 
 UPDATE inbox 
  SET state = 'PROCESSING'
  WHERE pk IN
      (SELECT pk 
        FROM   #QueuedInBoxRows)
COMMIT
 
SELECT pk FROM #QueuedInBoxRows
 
DROP TABLE #QueuedInBoxRows