How to implement a work queue in SQL server
How to implement a work queue in SQL server script is attached just below. Any comment is welcome.
/******************************************************************************
* Creates WorkQueue table *
******************************************************************************/
CREATE TABLE dbo.WorkQueue
(
WQID int NOT NULL,
Field1 varchar(10) NULL,
Field2 varchar(10) NULL,
Field3 varchar(10) NULL,
Field4 varchar(50) NULL,
Field5 varchar(50) NULL,
FieldN varchar(50) NULL,
Stage1Flag tinyint NOT NULL,
Stage2Flag tinyint NOT NULL,
Stage3Flag tinyint NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.WorkQueue ADD CONSTRAINT
DF_WorkQueue_Stage1Flag DEFAULT 0 FOR Stage1Flag
GO
ALTER TABLE dbo.WorkQueue ADD CONSTRAINT
DF_WorkQueue_Stage2Flag DEFAULT 0 FOR Stage2Flag
GO
ALTER TABLE dbo.WorkQueue ADD CONSTRAINT
DF_WorkQueue_Stage3Flag DEFAULT 0 FOR Stage3Flag
GO
ALTER TABLE dbo.WorkQueue ADD CONSTRAINT
PK_WorkQueue PRIMARY KEY CLUSTERED
(
WQID
) ON [PRIMARY]
GO
/******************************************************************************
* Creates WorkQueue index which will be used by TOP procedures *
* ATTENTION: Do not delete this index, since it is masterkey to the process *
******************************************************************************/
CREATE INDEX [IX_WorkQueue_Stages] ON [dbo].[WorkQueue]([Stage1Flag], [Stage2Flag], [Stage3Flag]) ON [PRIMARY]
GO
/******************************************************************************
* Creates WorkQueueErrors to hold errors which might occurs eventually *
******************************************************************************/
CREATE TABLE dbo.WorkQueueErrors
(
WQEID int NOT NULL IDENTITY (1, 1),
WQID int NOT NULL,
Stage tinyint NOT NULL,
Error varchar(255) NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.WorkQueueErrors ADD CONSTRAINT
PK_WorkQueueErrors PRIMARY KEY CLUSTERED
(
WQEID
) ON [PRIMARY]
GO
ALTER TABLE dbo.WorkQueueErrors ADD CONSTRAINT
FK_WorkQueueErrors_WorkQueue FOREIGN KEY
(
WQID
) REFERENCES dbo.WorkQueue
(
WQID
)
GO
/******************************************************************************
* Creates procedure WorkQueueStage1Top *
******************************************************************************/
CREATE PROCEDURE WorkQueueStage1Top -- Last Input First Output (LIFO)
AS
SELECT TOP 1 dbo.WorkQueue.*
FROM dbo.WorkQueue WITH (READPAST, ROWLOCK, UPDLOCK, INDEX(IX_WorkQueue_Stages))
WHERE (Stage1Flag = 0) -- 0 = not processed
RETURN
GO
/******************************************************************************
* Creates procedure WorkQueueStage1Pop *
******************************************************************************/
CREATE PROCEDURE WorkQueueStage1Pop
(@WQID int,
@Stage1Flag tinyint -- ATTENTION: parameter must be set to 1 (processed without error) or 2 (processed with error(s))
)
AS
UPDATE dbo.WorkQueue
SET Stage1Flag = @Stage1Flag
WHERE WQID = @WQID
RETURN
GO
/******************************************************************************
* Creates procedure WorkQueueStage2Top *
******************************************************************************/
CREATE PROCEDURE WorkQueueStage2Top -- Last Input First Output (LIFO)
AS
SELECT TOP 1 dbo.WorkQueue.*
FROM dbo.WorkQueue WITH (READPAST, ROWLOCK, UPDLOCK, INDEX(IX_WorkQueue_Stages))
WHERE (Stage1Flag = 1) AND -- 1 = processed without error
(Stage2Flag = 0) -- 0 = not processed
RETURN
GO
/******************************************************************************
* Creates procedure WorkQueueStage2Pop *
******************************************************************************/
CREATE PROCEDURE WorkQueueStage2Pop
(@WQID int,
@Stage2Flag tinyint -- ATTENTION: parameter must be set to 1 (processed without error) or 2 (processed with error(s))
)
AS
UPDATE dbo.WorkQueue
SET Stage2Flag = @Stage2Flag
WHERE WQID = @WQID
RETURN
GO
/******************************************************************************
* Creates procedure WorkQueueStage3Top *
******************************************************************************/
CREATE PROCEDURE WorkQueueStage3Top -- Last Input First Output (LIFO)
AS
SELECT TOP 1 dbo.WorkQueue.*
FROM dbo.WorkQueue WITH (READPAST, ROWLOCK, UPDLOCK, INDEX(IX_WorkQueue_Stages))
WHERE (Stage1Flag = 1) AND -- 1 = processed without error
(Stage2Flag = 1) AND -- 1 = processed without error
(Stage3Flag = 0) -- 0 = not processed
RETURN
GO
/******************************************************************************
* Creates procedure WorkQueueStage3Pop *
******************************************************************************/
CREATE PROCEDURE WorkQueueStage3Pop
(@WQID int,
@Stage3Flag tinyint -- ATTENTION: parameter must be set to 1 (processed without error) or 2 (processed with error(s))
)
AS
UPDATE dbo.WorkQueue
SET Stage3Flag = @Stage3Flag
WHERE WQID = @WQID
RETURN
GO
/******************************************************************************
* Creates procedure WorkQueueErrorsPush *
******************************************************************************/
CREATE PROCEDURE WorkQueueErrorsPush
(@WQID int,
@Stage tinyint,
@Error varchar(255)
)
AS
INSERT INTO dbo.WorkQueueErrors
(WQID, Stage, Error)
VALUES
(@WQID, @Stage, @Error)
RETURN
GO
/******************************************************************************
* Creates procedure WorkQueueErrorsGetStage1 *
******************************************************************************/
CREATE PROCEDURE WorkQueueErrorsGetStage1
AS
SELECT dbo.WorkQueue.*,
dbo.WorkQueueErrors.*
FROM dbo.WorkQueue WITH (READPAST, ROWLOCK, UPDLOCK, INDEX(IX_WorkQueue_Stages)) INNER JOIN
dbo.WorkQueueErrors ON dbo.WorkQueue.WQID = dbo.WorkQueueErrors.WQID
WHERE (dbo.WorkQueue.Stage1Flag = 2) AND -- 2 = processed with error(s)
(dbo.WorkQueueErrors.Stage = 1)
RETURN
GO
/******************************************************************************
* Creates procedure WorkQueueErrorsGetStage2 *
******************************************************************************/
CREATE PROCEDURE WorkQueueErrorsGetStage2
AS
SELECT dbo.WorkQueue.*,
dbo.WorkQueueErrors.*
FROM dbo.WorkQueue WITH (READPAST, ROWLOCK, UPDLOCK, INDEX(IX_WorkQueue_Stages)) INNER JOIN
dbo.WorkQueueErrors ON dbo.WorkQueue.WQID = dbo.WorkQueueErrors.WQID
WHERE (dbo.WorkQueue.Stage1Flag = 1) AND -- 1 = processed without error
(dbo.WorkQueue.Stage2Flag = 2) AND -- 2 = processed with error(s)
(dbo.WorkQueueErrors.Stage = 2)
RETURN
GO
/******************************************************************************
* Creates procedure WorkQueueErrorsGetStage3 *
******************************************************************************/
CREATE PROCEDURE WorkQueueErrorsGetStage3
AS
SELECT dbo.WorkQueue.*,
dbo.WorkQueueErrors.*
FROM dbo.WorkQueue WITH (READPAST, ROWLOCK, UPDLOCK, INDEX(IX_WorkQueue_Stages)) INNER JOIN
dbo.WorkQueueErrors ON dbo.WorkQueue.WQID = dbo.WorkQueueErrors.WQID
WHERE (dbo.WorkQueue.Stage1Flag = 1) AND -- 1 = processed without error
(dbo.WorkQueue.Stage2Flag = 1) AND -- 1 = processed without error
(dbo.WorkQueue.Stage3Flag = 2) AND -- 2 = processed with error(s)
(dbo.WorkQueueErrors.Stage = 3)
RETURN
GO