-- For the demo
USE tempdb;
GO
IF      OBJECT_ID(N'dbo.WorkQueue', N'U')
        IS NOT NULL
        DROP    TABLE dbo.WorkQueue;

IF      OBJECT_ID(N'dbo.GetWorkItem', N'P')
        IS NOT NULL
DROP    PROCEDURE dbo.GetWorkItem;

IF      OBJECT_ID(N'dbo.SetWorkItemComplete', N'P')
        IS NOT NULL
DROP    PROCEDURE dbo.SetWorkItemComplete;

-- Create table
CREATE  TABLE dbo.WorkQueue
    (
    work_queue_id   INTEGER IDENTITY(1,1) NOT NULL
                        PRIMARY KEY,
    name            VARCHAR(255) NOT NULL,
    status_id       TINYINT NOT NULL
                        CHECK (status_id IN (0, 1, 2)),
    status_desc     AS
                    ISNULL
                    (
                        CASE status_id
                            WHEN 0 THEN 'Unprocessed'
                            WHEN 1 THEN 'In progress'
                            WHEN 2 THEN 'Processed'
                            ELSE NULL
                        END, 
                    '')
    );

-- Add data to table
INSERT  dbo.WorkQueue
        (name, status_id)
SELECT  'A', 0  UNION ALL
SELECT  'B', 0  UNION ALL
SELECT  'C', 0  UNION ALL
SELECT  'D', 0  UNION ALL
SELECT  'E', 0  UNION ALL
SELECT  'F', 0  UNION ALL
SELECT  'G', 0  UNION ALL
SELECT  'H', 0  UNION ALL
SELECT  'I', 0  UNION ALL
SELECT  'J', 0  UNION ALL
SELECT  'K', 0;
GO
-- Index to help find unprocessed items
CREATE  NONCLUSTERED INDEX nc1
ON      dbo.WorkQueue (status_id);

CREATE  PROCEDURE dbo.GetWorkItem
        @WorkQueueID INTEGER OUTPUT
AS
BEGIN
        SET NOCOUNT ON;

        -- Allocate a work item and set it to in-progress
        -- TODO: Add lock timeout and error handler
        UPDATE  Q
        SET     status_id = 1,
                @WorkQueueID = work_queue_id
        FROM    (
                SELECT  TOP (1)
                        Q2.work_queue_id,
                        Q2.status_id
                FROM    dbo.WorkQueue Q2 WITH (UPDLOCK, READPAST, ROWLOCK)
                WHERE   Q2.status_id = 0
                ORDER   BY
                        Q2.work_queue_id ASC
                ) Q;
                
        RETURN  CASE WHEN @WorkQueueID IS NULL THEN 999 ELSE 0 END
END;
GO
CREATE  PROCEDURE dbo.SetWorkItemComplete
        @WorkQueueID INTEGER
AS
BEGIN
        SET NOCOUNT ON;

        -- Set the work item's status to complete 
        -- TODO: Add lock timeout and error handler
        UPDATE  Q
        SET     status_id = 2
        FROM    WorkQueue Q
        WHERE   Q.work_queue_id = @WorkQueueID
        AND     Q.status_id = 1;

        RETURN  CASE WHEN @@ROWCOUNT = 0 THEN 999 ELSE 0 END;
END;
GO

DECLARE @WorkItemID INTEGER,
        @ReturnCode INTEGER;

-- Get a work item        
EXECUTE @ReturnCode =
        dbo.GetWorkItem @WorkItemID OUTPUT;

-- Show the record
SELECT  rc = @ReturnCode,
        *
FROM    dbo.WorkQueue
WHERE   work_queue_id = @WorkItemID;

-- Simulate processing delay
WAITFOR DELAY '00:00:03';

-- Set the work item to complete
EXECUTE @ReturnCode =
        dbo.SetWorkItemComplete @WorkItemID;

-- Show the record
SELECT  rc = @ReturnCode,
        *
FROM    dbo.WorkQueue
WHERE   work_queue_id = @WorkItemID;

GO
DROP    TABLE dbo.WorkQueue;
DROP    PROCEDURE dbo.GetWorkItem;
DROP    PROCEDURE dbo.SetWorkItemComplete;

rc  work_queue_id   name    status_id   status_desc
0   1               A       1           In progress
...
0   1               A       2           Processed

SELECT TOP(1)
 @work_queue_id = work_queue_id, @name = name
 FROM
 work_queue
 WHERE processed_flag = 0