Hi John,

You may be well past this by now. But on the off-chance...

There are a good many patterns that your activated stored
procedure can implement to process the messages on its
queue.

What happens under the covers can be revealed by using SQL
Profiler.

When this statement:
 CREATE QUEUE [RequestQueue]
     WITH STATUS = ON,
     ACTIVATION (
         STATUS = ON,
         PROCEDURE_NAME = [ProcessRequests],
         MAX_QUEUE_READERS = 1,
         EXECUTE AS SELF
     );

is executed, or later when an ALTER QUEUE statement re-
enables the queue with activation, Service Broker creates a
thread that starts the stored procedure.

One pattern that works involves no WAITFOR statement. This
works great if there is a message waiting in the queue. The
RECEIVE TOP(1) statement retrieves the message immediately,
processes it, and the stored procedure exits. When the next
message is written to the queue, Service Broker activates
the procedure, it retrieves the message, processes it,
exits, and so on. If your application messaging model
includes not too many messages, this simple approach may be
okay.

There is a cost associated with activation. Another pattern
involves using a WHILE loop and a WAITFOR. In this pattern,
the activated stored procedure runs continuously as long as
there are messages waiting to be read from the queue. If it
finds that the queue is empty, it will wait for a while (an
interval worth tuning), to see if a message shows up. If it
does receive a message within the WAITFOR timeout, it will
process it, and continue the loop. If the timeout expires
before a new message arrives, the loop exits, and the stored
procedure exits. When a new message is delivered, Service
Broker activates the stored procedure, and the process of
looping and waiting begins again. If your application
messaging model includes multiple message conversations, or
a fairly spiky message load, this approach is pretty
efficient.

Other patterns serve application messaging models in which
there are bursts of messages, continuous high volume, or
scenarios in which queues are disabled during the day and
processing has to catch up during the night.

There is no one-size-fits-all pattern.

However, it is important to handle errors. And error
handling can make a simple pattern look a bit raggedy.

Here is the pattern I have found to work well in the
application I am building:

-----

CREATE PROCEDURE ProcessRequests
AS
 DECLARE @lclConversationHandle UNIQUEIDENTIFIER
 DECLARE @lclMessageTypeName NVARCHAR(256);
 DECLARE  @lclMessageBody XML;
 DECLARE @lclResponseMessage XML;

 WHILE (1=1)                 -- Loop as long as there are messages in the
queue
 BEGIN

     BEGIN TRANSACTION       -- Interact with the queue transactionally

     WAITFOR (
       RECEIVE TOP(1)        -- Receive messages from queue one at a time.
         @lclConversationHandle = conversation_handle,
         @lclMessageTypeName = message_type_name,
         @lclMessageBody = CAST(message_body AS XML)
       FROM TargetQueue
     ), TIMEOUT 60000;       -- Don't wait forever. After a minute, stop
waiting

     IF (@@ROWCOUNT = 0)     -- This condition results from a timeout
(generally)
     BEGIN                   -- We have exited the waitfor and have no
messages.
       ROLLBACK TRANSACTION; -- Rollback (nothing ventured, nothing gained).
       BREAK;                -- Break out of the while (1=1) loop. Service
Broker
     END                     -- will activate this stored procedure again
when
                             -- messages are delivered to its queue.

     SAVE TRANSACTION MessageReceived; -- This save point lets the procedure
                             -- rollback everything *except* the receive
from the
                             -- queue, should an unrecoverable error occur
in processing
                             -- the message.

     IF (@lclMessageTypeName = 'http://www.bar.foo.com/RequestMessage')
     BEGIN                   -- Attempt to handle this message.

       BEGIN TRY             -- Do whatever processing is required
                             -- knowing that if an error occurs, the catch
block below
                             -- will get control.
         -- ... process the request ...
         -- Construct the response message
         -- ... prepare response ...
         -- Send the response
         SEND ON CONVERSATION @lclConversationHandle
           MESSAGE TYPE [http://www.bar.foo.com/ResponseMessage]
             (@lclResponseMessage);

         -- End the conversation
         END CONVERSATION @lclConversationHandle;

       END TRY

       BEGIN CATCH           -- Determine if the error is recoverable
         IF (ERROR_NUMBER() = 1205)  -- Deadlock error. This is
recoverable, so
         BEGIN
           ROLLBACK TRANSACTION;     -- Rollback the whole transaction,
including the
           CONTINUE;                 -- read from the queue, and continue
the while loop
         END
         ELSE                -- A non recoverable error occurred. To avoid
poisoning the
         BEGIN               -- queue, rollback to the save point after the
queue was
                             -- read (so the unprocessable message is not
re-read).
           ROLLBACK TRANSACTION MessageReceived;
           -- Any other error logging as appropriate ...
         END
       END TRY
     END

     IF (@lclMessageTypeName =
'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
     BEGIN
       -- End the conversation
       END CONVERSATION @lclConversationHandle;
     END

     COMMIT TRANSACTION;     -- Commit either (1) everything, including the
queue
                             -- receive and whatever message processing
work was done,
                             -- or (2) just the queue receive after the
catch block
                             -- rolled back a non recoverable error to the
save point
                             -- at MessageReceived
 END -- WHILE (1=1)

GO

-----

Hope that is useful.

Thanks,
--Craig.