declare @DatabaseName nvarchar(50)
set @DatabaseName = N'ServiceBrokerMessaging';

--PREPARE SERVICE BROKER

--check if broker is enabled
if not exists (select name, Is_Broker_Enabled from sys.databases where name = @DatabaseName and Is_Broker_Enabled = 1)
begin
  --turn it on
  exec('alter database [' + @DatabaseName + '] set enable_broker with rollback immediate');
end

--Create activation procedure
IF EXISTS (SELECT * FROM sys.objects WHERE name = N'proc_BrokerTargetActivProc')
BEGIN
 DROP PROCEDURE proc_BrokerTargetActivProc 
END
GO

CREATE PROCEDURE proc_BrokerTargetActivProc
AS
  DECLARE @RecvReqDlgHandle UNIQUEIDENTIFIER;
  DECLARE @RecvReqMsg NVARCHAR(100);
  DECLARE @RecvReqMsgName sysname;

  WHILE (1=1)
  BEGIN

    BEGIN TRANSACTION;

    WAITFOR
    ( RECEIVE TOP(1)
        @RecvReqDlgHandle = conversation_handle,
        @RecvReqMsg = message_body,
        @RecvReqMsgName = message_type_name
      FROM TASK_QUEUE
    ), TIMEOUT 5000;

    IF (@@ROWCOUNT = 0)
    BEGIN
      ROLLBACK TRANSACTION;
      BREAK;
    END
     
    declare @targetpProcedure nvarchar(100);
    set @targetpProcedure = CAST(@RecvReqMsg AS nvarchar(100))
    
    IF LEN(@targetpProcedure) > 0
    BEGIN
    
       exec @targetpProcedure;
    
       --do not respond
       --DECLARE @ReplyMsg xml;
       --SELECT @ReplyMsg = N'<ReplyMessage>OK</ReplyMessage>';
 
       --SEND ON CONVERSATION @RecvReqDlgHandle
       --       MESSAGE TYPE 
       --       [//SBM/ReplyMessage]
       --       (@ReplyMsg);
     
    END
    ELSE IF @RecvReqMsgName =
        N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
    BEGIN
       END CONVERSATION @RecvReqDlgHandle;
    END
    ELSE IF @RecvReqMsgName =
        N'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
    BEGIN
       END CONVERSATION @RecvReqDlgHandle;
    END
      
    COMMIT TRANSACTION;

  END
GO

IF EXISTS (SELECT * FROM sys.services WHERE name = N'//SBM/TargetService')
BEGIN
     DROP SERVICE
     [//SBM/TargetService];
END

IF EXISTS (SELECT * FROM sys.services WHERE name = N'//SBM/InitService')
BEGIN
     DROP SERVICE
     [//SBM/InitService];
END
        
IF EXISTS (SELECT * FROM sys.service_queues WHERE name = N'TASK_QUEUE')
BEGIN
     DROP QUEUE TASK_QUEUE;
END     
     
IF EXISTS (SELECT * FROM sys.service_contracts WHERE name = N'//SBM/MSGContract')
BEGIN
     DROP CONTRACT
     [//SBM/MSGContract];
END

IF EXISTS (SELECT * FROM sys.service_message_types WHERE name = N'//SBM/RequestMessage')
BEGIN
     DROP MESSAGE TYPE
     [//SBM/RequestMessage];
END

IF EXISTS (SELECT * FROM sys.service_message_types WHERE name = N'//SBM/ReplyMessage')
BEGIN        
     DROP MESSAGE TYPE
     [//SBM/ReplyMessage];
END

CREATE MESSAGE TYPE [//SBM/RequestMessage] VALIDATION=WELL_FORMED_XML; 
CREATE MESSAGE TYPE [//SBM/ReplyMessage] VALIDATION=WELL_FORMED_XML;

CREATE CONTRACT [//SBM/MSGContract]
(
 [//SBM/RequestMessage]
 SENT BY INITIATOR 
  ,[//SBM/ReplyMessage]
 SENT BY TARGET 
);  

CREATE QUEUE TASK_QUEUE
 WITH STATUS=ON,
 ACTIVATION (
  PROCEDURE_NAME = proc_BrokerTargetActivProc,
  MAX_QUEUE_READERS = 5,
  EXECUTE AS SELF);

CREATE SERVICE [//SBM/TargetService] ON QUEUE TASK_QUEUE([//SBM/MSGContract]);
CREATE SERVICE [//SBM/InitService] ON QUEUE TASK_QUEUE([//SBM/MSGContract]);

go

--Create messaging procedure
IF EXISTS (SELECT * FROM sys.objects WHERE name = N'proc_ExecuteProcedureAsync')
BEGIN
  DROP PROCEDURE proc_ExecuteProcedureAsync 
END
go

CREATE PROCEDURE proc_ExecuteProcedureAsync
@ProcedureName nvarchar(100) = ''
AS

DECLARE @InitDlgHandle UNIQUEIDENTIFIER;
DECLARE @RequestMsg xml;
BEGIN TRANSACTION;
BEGIN DIALOG @InitDlgHandle
     FROM SERVICE
      [//SBM/InitService]
     TO SERVICE
      N'//SBM/TargetService'
     ON CONTRACT
      [//SBM/MSGContract]
     WITH
         ENCRYPTION = OFF;
 
 SELECT @RequestMsg = @ProcedureName;
       
--Send the Message
SEND ON CONVERSATION @InitDlgHandle MESSAGE TYPE [//SBM/RequestMessage](@RequestMsg);

--Close conversation
END CONVERSATION @InitDlgHandle;

COMMIT TRANSACTION;
GO 
    

IF EXISTS (SELECT * FROM sys.objects WHERE name = N'test')
BEGIN
  DROP TABLE test 
END

CREATE TABLE [dbo].[test](
	[id] [int] NULL
) ON [PRIMARY]

GO


IF EXISTS (SELECT * FROM sys.objects WHERE name = N'proc_test')
BEGIN
  DROP PROCEDURE proc_test 
END
go

CREATE PROCEDURE [dbo].[proc_test] 

AS
BEGIN
   WAITFOR DELAY '00:00:10'
   insert into test(id) values(1);
END

GO