do we have to write while (1=1) in activation stored procedures for service broker queues always or it's an optional?

Question

e.g.:

Create Proc test_act_sp  
AS  
    BEGIN  
        WHILE (1=1)  
            BEGIN  
                WAITFOR  
                (  
                    RECIEVE TOP(1) FROM Queue  
                )  
            ........
            ........
            ........
        END
    END

Answer 1

Generally the code looks like this

Create Proc test_act_sp  
AS  
BEGIN  
    WHILE (1=1)  
        BEGIN  
            WAITFOR  
            (  
                RECIEVE TOP(1) ... FROM Queue  
            ) , TIMEOUT 3000; --Timeout value can be changed.

            IF (@@ROWCOUNT = 0)
            BEGIN
               BREAK; 
            END
        ........
        ........
        ........
    END
END

The activation procedure starts message processing within infinite loop, and if it doesn't recieve any messages within timout interval (3sec in the above example), the procedure will be terminated.

If we have a bulk of messages sent to queue, the activation procedure will be started and loaded into memory once for all the set (as it waits for the messages after activation), and will be shut down 3 seconds after the last recieved message. So the while loop in this case reduce overhead for the server.

It's possible to omit loop, but in this case the activation procedure will be triggered for each message in queue, which might cause performance issues.

Answer 2

There must be some boolean expression (somehting that is true or false) following he WHILE, it's the syntax of the command.

If you want to write a WHILE loop that goes on infinitely, you have to write some expression that will always return true. 1=1is one of them, but you can write anything that always return true (like 0=0, 10=10)

Answer 3

It depends on whether you want to continuously process the queue. The (1=1) is always true and creates an infinite loop within which it will wait for an item to appear in the queue and process it.

If you want to create an activation proc that only processes a single item, you wouldn't need the loop at all.