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

service-brokersql serversql-server-2008

The standard format for beginning a dialog is like this:

Declare @dialog_handle UNIQUEIDENTIFIER
BEGIN DIALOG CONVERSATION @dialog_handle
   FROM SERVICE initiator_1
   TO SERVICE 'target_1'
   ON CONTRACT MyContract ;

But I would like to make a flexible generic procedure that can send a particular conversation from any of a number of initiator services (say I have 10 potential initiator services that would be using the same stored procedure).

Create Procedure SendJoinRequest (@initiatorServiceName varchar)
AS
   Declare @dialog_handle UNIQUEIDENTIFIER
BEGIN DIALOG CONVERSATION @dialog_handle
   FROM SERVICE @initiatorServiceName
   TO SERVICE 'target_1'
   ON CONTRACT MyContract ;

I guess this won't work because the identifier of the service in

FROM SERVICE @initiatorServiceName

actually cannot be a string, and must be the actual name of the service… Is there an easy way to pass this as an argument? or do I have to nest all of this in a nasty execution of a concatenated string ?

@String = blahblah + blah blah + escapecharacters + blah
execute(@String)

Thanks for your help

Best Answer

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

Create Procedure SendJoinRequest 
   @from sysname, 
   @to sysname,
   @contract sysname,
   @messageType sysname,
   @body XML
AS
Declare @dialog_handle UNIQUEIDENTIFIER
BEGIN TRANSACTION
BEGIN DIALOG CONVERSATION @dialog_handle
   FROM SERVICE @from
   TO SERVICE @to
   ON CONTRACT @contract
   WITH ENCRYPTION = OFF;
SEND ON CONVERSATION @dialog_handle MESSAGE TYPE @messageType (@body);
COMMIT
Related Question