USE SodaDB
GO

DROP PROCEDURE inventory_response
GO

CREATE PROCEDURE inventory_response
AS
DECLARE @c UNIQUEIDENTIFIER;
DECLARE @h UNIQUEIDENTIFIER;
DECLARE @m XML;
DECLARE @t NVARCHAR(128);

WHILE 1 = 1
BEGIN

BEGIN TRANSACTION
WAITFOR(
   GET CONVERSATION GROUP @c FROM InvResponseQueue_L9A),
   TIMEOUT 100 ;

IF @c IS NULL 
  BEGIN
   ROLLBACK TRANSACTION;
   BREAK;
  END

WHILE 1 = 1
BEGIN
	RECEIVE TOP(1) 
        @h=conversation_handle, 
        @t=message_type_name,
		@m=CAST(message_body as XML) FROM InvResponseQueue_L9A 
     WHERE conversation_group_id = @c;

    IF @@rowcount <> 1 
		BREAK;
    IF @t = N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
    OR @t = N'http://schemas.microsoft.com/SQL/ServiceBroker/Error' 
    BEGIN 
		END CONVERSATION @h;
        CONTINUE
    END
    ELSE
    IF @t = N'InvResponseMsg_L9A'
    BEGIN
    -- Parse the XML return message for status and update the appropriate order_line_item
	IF @m.value('declare default element namespace "http://www.company.com/inventoryRequest";
		/InventoryReq/status', 'varchar(10)') = 'OK'
       UPDATE order_line_item SET in_stock = 1 
          WHERE order_id = @m.value('declare default element namespace "http://www.company.com/inventoryRequest";
		/InventoryReq/order', 'int') 
          AND product_id = @m.value('declare default element namespace "http://www.company.com/inventoryRequest";
		/InventoryReq/product', 'int')
	ELSE
       UPDATE order_line_item SET in_stock = 0 
          WHERE order_id = @m.value('declare default element namespace "http://www.company.com/inventoryRequest";
		/InventoryReq/order', 'int') 
          AND product_id = @m.value('declare default element namespace "http://www.company.com/inventoryRequest";
		/InventoryReq/product', 'int')

	END

END
COMMIT TRANSACTION;
END
go

ALTER QUEUE InvResponseQueue_L9A WITH ACTIVATION 
  (STATUS=ON,
   PROCEDURE_NAME=inventory_response,
   MAX_QUEUE_READERS=5,
   EXECUTE AS SELF
  )
go