Here is something I started working on a while back... As far as I know it was working.
--------------------------------- START
CREATE TABLE [dbo].[QM_QueueItem](
[QueueItemID] [bigint] IDENTITY(1,1) NOT NULL,
[QueueItemUID] [uniqueidentifier] NOT NULL,
[QueueID] [int] NOT NULL,
[DisplayName] varchar COLLATE Latin1_General_CI_AS NOT NULL,
[TextData] [text] COLLATE Latin1_General_CI_AS NULL,
[BinaryData] [image] NULL,
[Priority] [int] NOT NULL,
[Attempt] [int] NOT NULL,
[NextAttempt] [datetime] NOT NULL,
[CreatedDate] [datetime] NOT NULL,
[InProgress] [bit] NOT NULL,
[InternalUID] [uniqueidentifier] NOT NULL,
CONSTRAINT [PK_QM_Queue_QueueItemUID] PRIMARY KEY NONCLUSTERED
(
[QueueItemUID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY],
CONSTRAINT [UC_QM_Queue_InternalUID] UNIQUE NONCLUSTERED
(
[InternalUID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE PROCEDURE [dbo].[QM_BeginDequeue]
(
@QueueID int
)
AS
SET NOCOUNT ON
DECLARE @InternalUID uniqueidentifier
SET @InternalUID = newid();
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
DECLARE @NOW datetime
SET @NOW = GetDate()
UPDATE dbo.QM_Queue
SET InternalUID = @InternalUID, Attempt = Attempt + 1, InProgress = 1, NextAttempt = @NOW
WHERE QueueItemID
IN
(SELECT TOP 1 QueueItemID
FROM dbo.QM_Queue
WHERE InProgress = 0 AND NextAttempt <= @NOW AND QueueID = @QueueID
ORDER BY Priority DESC, CreatedDate, QueueItemID)
COMMIT TRAN
SELECT QueueItemUID, DisplayName, TextData, BinaryData, Priority, Attempt, NextAttempt, CreatedDate
FROM dbo.QM_Queue
WHERE InternalUID = @InternalUID
SET NOCOUNT OFF
GO
CREATE PROCEDURE [dbo].[QM_CancelEnqueue]
(
@QueueItemUID uniqueidentifier
)
AS
DELETE FROM dbo.QM_Queue
WHERE QueueItemUID = @QueueItemUID AND InProgress = 0
GO
CREATE PROCEDURE [dbo].[QM_Dequeue]
(
@QueueID int
)
AS
SET NOCOUNT ON
DECLARE @QueueUID uniqueidentifier
DECLARE @InternalUID uniqueidentifier
SET @InternalUID = newid();
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
DECLARE @NOW datetime
SET @NOW = GetDate()
UPDATE dbo.QM_Queue
SET InternalUID = @InternalUID, Attempt = Attempt + 1, InProgress = 1, NextAttempt = @NOW
WHERE QueueItemID
IN
(SELECT TOP 1 QueueItemID
FROM dbo.QM_Queue
WHERE InProgress = 0 AND NextAttempt <= @NOW AND QueueID = @QueueID
ORDER BY Priority DESC, CreatedDate, QueueItemID)
COMMIT TRAN
SELECT QueueItemUID, DisplayName, TextData, BinaryData, Priority, Attempt, NextAttempt, CreatedDate
FROM dbo.QM_Queue
WHERE InternalUID = @InternalUID
DELETE FROM dbo.QM_Queue
WHERE InternalUID = @InternalUID
SET NOCOUNT OFF
GO
CREATE PROCEDURE [dbo].[QM_EndDequeue]
(
@QueueItemUID uniqueidentifier
)
AS
DELETE FROM dbo.QM_Queue
WHERE @QueueItemUID = @QueueItemUID AND InProgress = 1
GO
CREATE PROCEDURE [dbo].[QM_Enqueue]
(
@QueueItemUID uniqueidentifier,
@QueueID int,
@DisplayName nvarchar(255),
@TextData text = NULL,
@BinaryData image = NULL,
@Priority int,
@NextAttempt datetime
)
AS
INSERT INTO dbo.QM_Queue (QueueItemUID, QueueID, DisplayName, TextData, BinaryData, Priority, Attempt, NextAttempt, CreatedDate, InProgress, InternalUID)
VALUES (@QueueItemUID, @QueueID, @DisplayName, @TextData, @BinaryData, @Priority, 0, @NextAttempt, GetDate(), 0, newid())
GO
CREATE PROCEDURE [dbo].[QM_ReEnqueue]
(
@QueueItemUID uniqueidentifier,
@Priority int,
@NextAttempt DateTime
)
AS
UPDATE dbo.QM_Queue
SET NextAttempt = @NextAttempt, Priority = @Priority, InProgress = 0
WHERE QueueItemUID = @QueueItemUID AND InProgress = 1
GO
CREATE PROCEDURE [dbo].[QM_ResetQueue]
AS
SET NOCOUNT ON
UPDATE dbo.QM_Queue
SET InProgress = 0
WHERE InProgress = 1
SET NOCOUNT OFF
--------------------------------- END
Marcus
