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