1

I'm creating a queuing system using a SQL Server in the back-end for an app. The plan is to create a FIFO type of queuing system similar to how like a fast food rood restaurant is set up. Customers would enter into a queue and will be serviced by X amount of workers. When a customer is processed, the next customer will be serviced by the available worker. I think my issue is similar to the one in this post but I'm not sure if it is the exact same scenario.

I looked at Rusano's "Using tables as Queues" article and my understanding is that I would need to do something similar to his FIFO example shown below:

create table FifoQueue 
(
    Id bigint not null identity(1,1),
    Payload varbinary(max)
);
go

create clustered index cdxFifoQueue on FifoQueue (Id);
go

create procedure usp_enqueueFifo
    @payload varbinary(max)
as
    set nocount on;

    insert into FifoQueue (Payload) values (@Payload);
go

create procedure usp_dequeueFifo
as
    set nocount on;

    with cte as 
    (
        select top(1) Payload
        from FifoQueue with (rowlock, readpast)
        order by Id
    )
    delete from cte
    output deleted.Payload;
go

Example of the queue structure

Status table:

1 = Waiting
2 = Getting served
3 = Processed

EnterQueue Time | LeaveQueue Time | Worker | Status
----------------+-----------------+--------+--------
11:22           | 11:24           |    1   |   3
11:23           |                 |    2   |   2
11:26           |                 |    1   |   2
11:27           |                 |   NULL |   1

The customers are added to the queue by a stored procedure. The next customer being served is based on their entry time.

Can I use a stored procedure based on the FIFO example to handle the processing/de-queuing of the customers?

Eques316
  • 41
  • 7
  • 2
    Unfortunately your question is both too broad and too opinion based to be suitable for SO. Have a read of this https://stackoverflow.com/help/on-topic and see if you can re-word your question so that its specific and not opinion based. – Dale K Apr 02 '19 at 03:08
  • Thanks for the feedback. I'll reformat my question to make it more specific. – Eques316 Apr 02 '19 at 19:01

0 Answers0