Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
add comment

1 Answer

up vote 5 down vote accepted

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
share|improve this answer
    
ahhh elegant, thanks. This prevents me from having to escape single quotes that are concatenated with variables and more single quotes elsewhere, which never works the first few tries :) –  fa1c0n3r Jun 6 '12 at 7:40
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.