Correct way to continuously receive from a queue

Question

I have to receive messages from a queue continuously.

My current code looks like this:

    DECLARE @status INT=1
    WHILE @status IS NOT NULL
    BEGIN
    WAITFOR (RECEIVE  @status=status,
            @message = message_body 
    
            FROM dbo.MyQueue) 
...

Of course this code never ends, I have to stop it manually, but it does what's needed.

I'm thinking on put this code in a SP invoked by a job, scheduled at a time, in order to have the call resuming in case of error.

What I would like to ask is:

• Is a job the correct way of starting the "infinite" listener?
• If a job is scheduled, let's say, every ten seconds, does it create "overlapped" processes?
• If I kill the job, is the invoked SP killed as well?
• If not, how can I kill running SP?

Answer 1

While the answer by Trubs does answer your questions and it might work great for you, I'd say the "correct" way to continuously receive from a queue is by using Service Broker Activation, either internal or external.

When using internal activation you attach a Stored Procedure to a queue and tell Service Broker to activate it when there are messages to be processed. The SP can terminate when there is no work left and it will be reactivated as needed. Also, you have the choice of telling the system to run multiple activations of your SP if there is demand. You configure this type of activation like this:

ALTER QUEUE dbo.MyQueue
WITH ACTIVATION (
    PROCEDURE_NAME = dbo.MyQueueReaderSP
    , MAX_QUEUE_READERS = 1
    , EXECUTE AS OWNER
    , STATUS = ON);

An example of using this feature can be seen on this article by Remus Rusanu. His site has many articles on Service Broker, definitely worth checking out. The code above was adapted from his site as well.

External activation is more complicated and has more moving pieces but it allows you to activate external processes. It's great in the fact you can run code written in any language and also take the processing load out of the database server itself.

Answer 2

• An agent job is a good "SQL Server" way to start an infinite job. as for your code, you could simply use

     While 1=1
  

• An agent job will not start another session while the existing one is still running

• Some SQL statements will be killed with the job, some not (see link)

• find the spid of the running command and kill it.