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

Question

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...

Answer 1

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.

Answer 2

This is a working sample receive procedure that is re-usable over different queues.

ALTER PROCEDURE [dbo].[zBroker_OnReceive_WFItemsUpdated_Queue]
AS
BEGIN
                SET NOCOUNT ON;

                DECLARE @h UNIQUEIDENTIFIER;
                DECLARE @messageType int;
                DECLARE @Packet VARCHAR(50);
                DECLARE @MyId UNIQUEIDENTIFIER;
                DECLARE @QueueName NVARCHAR(200);
                DECLARE @Sql NVARCHAR(2000);

                SET  @MyId = NEWID(); -- Just an Id for testing that get's written to the log table

                SET @QueueName = (
                                SELECT q.NAME
                                FROM                                
                                                sys.service_queues q (nolock)
                                JOIN                
                                                sys.dm_broker_activated_tasks t (nolock) ON t.spid = @@SPID AND q.object_id = t.queue_id
                )   
                SET @Sql = 'WAITFOR( RECEIVE TOP(1) 
                                                @h = conversation_handle,
                                                @messageType = message_type_id,
                                                @Packet = message_body
                                                FROM ' + @QueueName + '), TIMEOUT 1000;'

                --Clear the queue
                WHILE (1=1)
             BEGIN
                                BEGIN TRANSACTION;

                             EXECUTE sp_executesql @Sql
                                ,N'@h UNIQUEIDENTIFIER OUTPUT,@messageType int OUTPUT,@Packet VARCHAR(50) OUTPUT'
                                ,@h = @h OUTPUT
                                ,@messageType = @messageType OUTPUT
                                ,@Packet = @Packet OUTPUT


                                IF (@@ROWCOUNT = 0)
                                BEGIN
                                                COMMIT;
                                              BREAK;
                                END
                                ELSE
                                BEGIN

                                                IF  @messageType <= 2  
                                                BEGIN
                                                                END CONVERSATION @h;
                                                END
                                            ELSE 
                                                BEGIN

                                                                INSERT  INTO t_log (logdata,msgdata,ProcId,StartTime)
                                                                VALUES  ( @QueueName, @Packet ,@MyId,GETDATE());

                                                                WAITFOR DELAY '00:00:10';
                                                                UPDATE t_log SET EndTime = GETDATE()
                                                                WHERE logid = @@identity

                                                                END CONVERSATION @h;
                                            END                             

                                            COMMIT;
                                END
            END
END