Sql-server – How to make a flexible service broker receive in a stored sql procedure, how to pass “FROM” parameter into receive

dynamic-sqlservice-brokersql serversql-server-2008

a while ago I asked a question about how to make begin dialog and send more flexible so that it can be embedded in a procedure that takes the to, from, parameters as sysname variables.

Service Broker Procedure that will be used by multiple initiators to begin dialog conversation.. how to pass a parameter into it

however as Rusanu mentioned in the answer, this same technique cannot be used for the From clause of the Receive.

Actually it will work. Most SSB verbs accept parameters for they arguments (except queue name for RECEIVE, of course). The parameters are of type sysname …

actually the send side is done and I am now trying to make the RECEIVE flexible in the same way, something like:

CREATE PROCEDURE QueueReceive
  @myTargetQueue SYSNAME
  @cg UNIQUEIDENTIFIER OUTPUT
  @ch UNIQUEIDENTIFIER OUTPUT
  @msg XML OUTPUT
as
    BEGIN TRANSACTION;
    WAITFOR
            ( RECEIVE TOP(1)
                @cg = conversation_group_id,
                @msg = cast(message_body as XML),
                @ch= conversation_handle  
              FROM @myTargetQueue
            ), TIMEOUT 3000;
    COMMIT
...

It seems variables of type sysname cannot be used in the from clause of the RECEIVE? If I have to do it in dynamic SQL, how would I return all the variables, conversation_group_id, conversation_handle, etc. from the dynamic sql execute of a receive function? is there a better performing technique to accomplish the same thing?

Thank you.

FIX/Update SO FAR:
I am creating a bunch of IF clauses depending on what the passed parameter is, it will just execute a different whole receive statement. It is not efficient because I have to change procedure code any time I add a new QUEUE, but I guess it will do for now…

Best Answer

Because RECEIVE is basically a DELETE and as such has a query plan, it must obey the same restrictions SELECT/INSERT/DELETE/UPDATE statements have, specifically the restrictions that the object it acts on must be known at compile time, not at execution time.

The only option is to use dynamic SQL, with all the blessings and pitfalls that follow.

You could also generate the procedure body during project deployment, have a single template and generate a specific procedure for each queue, specialized for the specific queue name. Whether this is feasible depends on many factors, first and foremost on the organization of your project and how you deploy.

On a side note I'm surprised to hear that you have many queues. In general the tendency is to have a single queue and several queue readers (activated procedures). Since SSB programing is event driven (wait for for message, process message, wait for message, process message, wait for message...) having more than one queue to wait for messages on it becomes harder, as the application now has to wait on multiple sources (eg. one thread per queue at least). Even with SSB activation, which alleviates the need for explicit wait wince it launches the code to process the message on-demand, multiple queues are harder to manage (max_queue_readers per queue adds up to perhaps too many internal activated procedures launched). Consider using a single service and queue on the RECEIVE side. Even when multiple services are needed (for whatever reason) they can be consolidated on a single queue.

Related Question