SQL Server Service Broker Sample

CREATE DATABASE GM
---------------------------------------------------
CREATE TABLE [dbo].[GMBulkOperationLog](
    [FromSerialNo] [varchar](20)  NOT NULL,
    [ToSerialNo] [varchar](20)  NOT NULL,
    [Amount] [int] NOT NULL,
    [Action] [int] NOT NULL,
    [Status] [tinyint] NOT NULL
) ON [PRIMARY]

--------------------------------------------------------
CREATE PROCEDURE [dbo].[GM_SB_InitGMBulk]
(
 @Data XML
)
AS
BEGIN

 DECLARE @Handle UNIQUEIDENTIFIER;

 BEGIN DIALOG CONVERSATION @Handle
 FROM SERVICE GMBulkInitService
 TO SERVICE 'GMBulkTargetService'
 ON CONTRACT GMBulkContract
 WITH ENCRYPTION = OFF;

 SEND ON CONVERSATION @Handle 
 MESSAGE TYPE [GMBulkMsgType](@Data);

END

-----------------------------------------------------
CREATE PROCEDURE [dbo].[GM_SB_ProcessInternalGMBulk]
AS 
BEGIN

 SET NOCOUNT ON;
 DECLARE @Handle UNIQUEIDENTIFIER;
 DECLARE @MessageType SYSNAME;
 DECLARE @Message XML

 DECLARE @MaxCounter INT
 DECLARE @Counter INT

 DECLARE @From VARCHAR(20)
 DECLARE @To VARCHAR(20)
 DECLARE @Action INT
 DECLARE @Amount INT;

 RECEIVE TOP (1) 
 @Handle = conversation_handle,
 @MessageType = message_type_name, 
 @Message = message_body
 FROM GMBulkProcessQueue;

 IF(@Handle IS NOT NULL AND @Message IS NOT NULL)
 BEGIN
 
     END CONVERSATION @Handle

     DECLARE @TmpTbl TABLE 
     (
        ID INT IDENTITY(1,1),
        FromSerialNo VARCHAR(20),
        ToSerialNo VARCHAR(20)
     )

     INSERT INTO @TmpTbl(FromSerialNo,ToSerialNo)
     SELECT T.Item.value('@from','varchar(20)') AS FromSerialNo,
            T.Item.value('@to','varchar(20)') AS ToSerialNo
     FROM @Message.nodes('/data[1]/ranges[1]/sn') AS T(Item)
    

     SELECT @Amount = CAST(CAST(@Message.query('/data/amount/text()') AS NVARCHAR(MAX)) AS INT)
     SELECT @Action = CAST(CAST(@Message.query('/data/action/text()') AS NVARCHAR(MAX)) AS INT)

     SELECT @MaxCounter = MAX(ID) FROM @TmpTbl
    
     SET @Counter = 1
     WHILE( @Counter <= @MaxCounter)
     BEGIN
        
        SELECT @From = FromSerialNO, @To = ToSerialNo 
        FROM @TmpTbl
        WHERE ID = @Counter
        
        INSERT INTO GMBulkOperationLog(FromSerialNo,ToSerialNo, Amount, [Action], Status)
        VALUES(@From,@To,@Amount,@Action, 0);
        ---------------------------------------
        --processing
        ---------------------------------------
        UPDATE GMBulkOperationLog
        SET Status = 1
        WHERE FromSerialNo = @From AND ToSerialNo = @To

        SET @Counter = @Counter + 1
    END
 END

END
----------------------------------------------------------------
CREATE PROCEDURE [dbo].[GM_SB_CloseInitConversation]
AS
BEGIN
    DECLARE @Handle UNIQUEIDENTIFIER;
    DECLARE @MessageType SYSNAME;
    DECLARE @Message XML;
 
 
    WAITFOR (
    RECEIVE TOP (1)
        @Handle = CONVERSATION_HANDLE,
        @MessageType = message_type_name,
        @Message = message_body
        FROM GMBulkQueue
    ), TIMEOUT 30000 
    
    IF (@@ROWCOUNT > 0)
    BEGIN
        IF @MessageType = N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
        BEGIN
            END CONVERSATION @Handle;
        END
    END
END
-------------------------------------------------------------------

ALTER DATABASE GM SET ENABLE_BROKER

CREATE MESSAGE TYPE GMBulkMsgType VALIDATION = NONE
CREATE CONTRACT [GMBulkContract] ([GMBulkMsgType] SENT BY INITIATOR) 

CREATE QUEUE [GMBulkProcessQueue]
CREATE SERVICE [GMBulkTargetService] ON QUEUE [GMBulkProcessQueue]([GMBulkContract])

CREATE QUEUE [GMBulkQueue]
CREATE SERVICE [GMBulkInitService] ON QUEUE [GMBulkQueue]

ALTER QUEUE [GMBulkQueue] 
WITH ACTIVATION 
(STATUS = ON,
MAX_QUEUE_READERS = 1,
PROCEDURE_NAME = GM_SB_CloseInitConversation,
EXECUTE AS OWNER)

ALTER QUEUE [GMBulkProcessQueue] 
WITH ACTIVATION 
(STATUS = ON,
MAX_QUEUE_READERS = 1,
PROCEDURE_NAME = GM_SB_ProcessInternalGMBulk,
EXECUTE AS OWNER)
---------------------------------------------------------

DECLARE @d XML
SET @d = N'
<data>
<ranges>
<sn from="11111" to="22222" />
<sn from="44444" to="55555" />
</ranges>
<amount>200</amount>
<action>2</action>
</data>'

EXEC dbo.GM_SB_InitGMBulk @Data = @d

SELECT * FROM GMBulkOperationLog

TRUNCATE TABLE GMBulkOperationLog




--If database has active connections, 
--we can not enable service broker,
--to do that database needs exclusive lock,
--To kill all sessions, we can run this:

ALTER DATABASE GM SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE GM SET ENABLE_BROKER
ALTER DATABASE GM SET MULTI_USER