USP_ReceiveAndRespondBrokerMessage

Procedure to tidy service broker message response and conversation closure.
Requires TBL_ServiceBrokerAudit for optional message auditing.

CREATE PROCEDURE [dbo].[USP_ReceiveResponseAndCloseConversation]
@Queue NVARCHAR(200)
,@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 Response

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_ReceiveResponseAndCloseConversation')
END

-- Uncomment next line to debug
-- SELECT @RecvReqMsg AS ReceivedRequestMsg;

SET @SqlString = ' END CONVERSATION ''' + CAST(@RecvReqDlgHandle AS NVARCHAR(MAX)) + ''';'
EXEC sp_executesql @SqlString


COMMIT TRANSACTION;
END
GO

Usage :

exec USP_ReceiveAndRespondBrokerMessage
@Queue = N'AdventureWorksTargetQueue'
,@MessageType = N'http://www.servicebroker.com/SendType'
,@ResponseType = N'http://www.servicebroker.com/ResponseType'
,@ResponseMessage = N'Reply Service Broker Message from AdventureWorksTarget'
,@Audit = 1
go