Activation procedure design

I've been experimenting with Service Broker and was surprised at one aspect of the design: the interface to Activation stored procedures.

I would have expected the queue to be a parameted passed to the procedure rather than having to hard code the queue query into the SP.

In a system with lots of queues it seems plausible that the same activation procedure might want to be used with several queues.

Any comments?

David.

Stored procedures are compiled into execution plans that bind strictly the rowsets being involved. That means that a stored procedure cannot be compiled to issue a RECEIVE (or SELECT for the matter) against a generic 'queue', but only agains a very specific <queue_name>. This is the same reason why one cannot write a SELECT where the table name is a @.variable. The only workaround, both in SELECT and in activation case, is to use dynamic SQL, with the likely cost of having to compile the dynamic SQL when the procedure executes.

Given this it would not make sense to have the queue name passed as a parameter, it is not a performant pattern. However, if you must, there is the trick to get the queue you were activated for from sys.dm_broker_activated_tasks and build dynamic SQL to RECEIVE the messages.

HTH,

~ Remus