Setup:

-- 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);

Procedures:

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

Test:

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;

Results:

rc work_queue_id name status_id status_desc

0 1 A 1 In progress

...

0 1 A 2 Processed

Allocation routine query plan: (compute scalars removed)