Topics for SEND and BEGIN DIALOG CONVERSATION fails to mention that they accept variables. - by Erland Sommarskog

Status : 

 


4
0
Sign in
to vote
ID 717322 Comments
Status Active Workarounds
Type Bug Repros 0
Opened 1/7/2012 2:05:39 AM
Access Restriction Public

Description

When you read the syntax diagrams for BEGIN DIALOG CONVERSTATION and SEND, you get the impression that these commands does not accept variables for these tokens:
initiator_service_name
'target_service_name'
'service_broker_guid'
contract_name
(all BEGIN DIALOG CONVERSATION)
message_type_name (SEND)
Nevertheless the below is valid syntax:

CREATE PROCEDURE send_messages @sender_service sysname, 
                               @con            sysname, 
                               @mtyp           sysname,
                               @i              int AS
                               
DECLARE @dialog uniqueidentifier

BEGIN DIALOG CONVERSATION @dialog
FROM  SERVICE @sender_service
TO    SERVICE 'receiver_service'
ON CONTRACT @con
WITH ENCRYPTION = OFF

; SEND ON CONVERSATION @dialog MESSAGE TYPE @mtyp
  (convert(varbinary(8000), 'Message ' + ltrim(str(@i))))
  
END CONVERSATION @dialog  
go

To be fair, nor do the topics rule out the use of variables. But it has a little do with expectations. I didn't list the conversation_handle arguments to SEND and RELATED_CONVERSATION, but the same applies to these, but here the reader execpts to be able to use a variable, since anything else would be crazy. 

The arguments initiator_service_name, contract_name and message_type are all identifiers. They other two I listed are strings, but one of them holds an identifier. Normally, in T-SQL you cannot substitute an identifier with a variable. There are exceptions like "EXEC @spname", but it is nothing you can take for granted. Thus, where this is permitted, it should be clear from the syntax diagram. (And this also applies when then argument is a string, a number and so on. There are plenty full of T-SQL commands that accepts literals but do not accept variables, for instance CREATE CERTIFICATES FROM BINARY.)

A second note on these two topics is that the Permissions sections seems incomplete. Somehow, it should be said that ownership chaining applies, so that if the comands are in a stored procedure, the user does not need any permissions at all.
Sign in to post a comment.
Posted by Microsoft on 10/16/2012 at 11:49 AM
Thank you for the SQL Server documentation feedback. We will investigate and update the documentation as appropriate in a future update to Books Online.
Derrick VanArnam
SQL Server Technical Writer