The other way to automate this stored procedure is by attaching it to the queue itself so that it Service Broker directly executes the procedure in a process called activation. With activation Service Broker starts an application whenever there is work to do (e.g. when messages are in the queue). When we enable activation for a queue, SQL Server spawns a queue monitor process to keep an eye on the queue and check every few seconds to see whether there are messages available for processing. There are 2 types of activation:
Internal activation – A stored procedure like the one above is attached to the queue to process messages automatically as they arrive.
External activation – An external program is notified that messages need to be processed.
Colleen focuses on internal activation, which calls a stored procedure for each message received. Check her post out.