USP_ReceiveResponseAndCloseConversation

Procedure to tidy receiving & responding to a service broker message.
Requires TBL_ServiceBrokerAudit for optional message auditing. -

CREATE PROCEDURE [dbo].[USP_ReceiveAndRespondBrokerMessage]
@Queue NVARCHAR(200),
@MessageType NVARCHAR(200),
@ResponseType NVARCHAR(200),
@ResponseMessage NVARCHAR(4000),
@Audit BIT

AS

BEGIN

DECLARE @RecvReqDlgHandle UNIQUEIDENTIFIER;
DECLARE @RecvReqMsg XML;
DECLARE @RecvReqMsgName sysname;
DECLARE @SqlString NVARCHAR(MAX);
DECLARE @Parameters NVARCHAR(MAX);

SET NOCOUNT ON

BEGIN TRANSACTION;
-- Receive Message

SET @Parameters = ' @RecvReqDlgHandle UNIQUEIDENTIFIER OUTPUT,
       @RecvReqMsg XML OUTPUT,
       @RecvReqMsgName NVARCHAR(MAX) OUTPUT'

SET @SqlString = ' WAITFOR
    ( RECEIVE TOP(1)
     @RecvReqDlgHandle = conversation_handle,
     @RecvReqMsg = message_body,
     @RecvReqMsgName = message_type_name
      FROM [' + @Queue + ']
    ), TIMEOUT 1000;'

EXEC sp_executesql
@SqlString,
@Parameters,
@RecvReqDlgHandle = @RecvReqDlgHandle OUTPUT,
@RecvReqMsg = @RecvReqMsg OUTPUT,
@RecvReqMsgName = @RecvReqMsgName OUTPUT

IF @Audit = 1
BEGIN
INSERT INTO [dbo].[TBL_ServiceBrokerAudit]
     ([Queue]
     ,[MessageHandle]
     ,[MessageName]
     ,[MessageContent]
     ,[AuditSource])
  VALUES
     (@Queue
     ,@RecvReqDlgHandle
     ,@RecvReqMsgName
     ,@RecvReqMsg
     ,'USP_ReceiveAndRespondBrokerMessage')
END
-- Display received message
SELECT @RecvReqMsg AS ReceivedRequestMsg;

IF @RecvReqMsg IS NOT NULL
BEGIN
-- If received message is of the correct type, send response
IF @RecvReqMsgName = @MessageType

BEGIN
  SET @SqlString = ' SEND ON CONVERSATION ''' + CAST(@RecvReqDlgHandle AS NVARCHAR(MAX)) + '''
        MESSAGE TYPE
       [' + @ResponseType + '] (''' + @ResponseMessage + ''');
      END CONVERSATION ''' + CAST(@RecvReqDlgHandle AS NVARCHAR(MAX)) + ''';'

  EXEC sp_executesql @SqlString
  -- Uncomment next line to debug
  --SELECT @ResponseMessage AS SentReplyMsg;

END
ELSE
 SELECT 'Not Expected Message Type'
END
ELSE
SELECT 'No Message to Receive'
COMMIT TRANSACTION;

END
GO

Usage :

exec adventureworks.dbo.USP_ReceiveResponseAndCloseConversation
  @Queue = N'AdventureWorksSourceQueue'
 ,@Audit = 1
go