USE SodaDB
GO

--
-- We're replacing a 
-- Traditional transaction update orders and inventory
-- see if this works
--
DECLARE @orderform XML

BEGIN TRANSACTION
	INSERT INTO orders VALUES(347, GETDATE()) 
	INSERT INTO order_form VALUES(SCOPE_IDENTITY(), @orderform);

	UPDATE	inventory 
	SET		units_in_stock = units_in_stock - 3
	WHERE	product_id = 1040;
COMMIT
go

SELECT * FROM inventory
go

-- END TRADITIONAL TRANSACTION

drop proc update_inventory
GO
-- Encapsulate the broker step into a stored procedure
CREATE PROCEDURE update_inventory(@product_id INT, @units_ordered INT)
AS
DECLARE @h UNIQUEIDENTIFIER;
-- hard code this for now
DECLARE @invreq XML;
SET @invreq = '
<InventoryReq xmlns="http://www.company.com/inventoryRequest">
  <order>1</order>
  <product>1040</product>
  <units>3</units>
  <status>Req</status>
</InventoryReq>';

BEGIN TRY
BEGIN TRANSACTION;
BEGIN DIALOG CONVERSATION @h
 FROM SERVICE InvInitiator_L9A
 TO SERVICE 'InvService_L9A'
 ON CONTRACT InvContract_L9A
 WITH ENCRYPTION = OFF;

-- request for product 1040
SEND ON CONVERSATION @h 
 MESSAGE TYPE InvRequestMsg_L9A (@invreq);
COMMIT;
END TRY
BEGIN CATCH
	PRINT ERROR_MESSAGE()
    ROLLBACK;
END CATCH
GO

--
-- Rewrite initiator for process into procedure
--

DECLARE @i INT
SET @i = 1
WHILE @i < 2
 BEGIN
   DECLARE @orderform XML
   BEGIN TRANSACTION
   INSERT INTO orders VALUES(347, GETDATE()) 
   INSERT INTO order_form VALUES(SCOPE_IDENTITY(), @orderform);
   EXECUTE update_inventory 1040, 3
   COMMIT
   SET @i = @i + 1
 END
go

SELECT * FROM inventory