USP_BBWORKFLOW_SERVICE_GET_SSB_QUEUE_NAME

create procedure dbo.USP_BBWORKFLOW_SERVICE_GET_SSB_QUEUE_NAME

  @SERVICENAME nvarchar(128)



/*
Returns the name of the Sql Service Broker Queue used by a Service Broker service
*/

as

   /* 

   The VIEW DEFINITION permission must have been granted to the BBAPPFXWORKFLOWSERVICEROLE for the service whose name = @SERVICENAME
   For example, if @SERVICENAME= 'BBAPPFX_WORKFLOW_MESSAGE_TARGET_SERVICE' then this permission must have been granted:
       GRANT VIEW DEFINITION on SERVICE::BBAPPFX_WORKFLOW_MESSAGE_TARGET_SERVICE to BBAPPFXWORKFLOWSERVICEROLE;

   */

  select SCHEMA_NAME(q.[schema_id]) as 'schema_name' ,q.[name] as 'queue_name' from sys.service_queues as q 
    inner join sys.services as s on q.object_id=s.service_queue_id
    where s.name = @SERVICENAME;