create procedure CreateOrder
    @orderXml XML
as
begin try
    -- start a transaction
    begin transaction;
    -- declare variables
    declare @e varchar(128);
    declare @ecode int;
    declare @orderId int;
    declare @customerId int;
    declare @totalPrice decimal(10,2);
    declare @mycount int;
    declare @productId int;
    declare @quantity int;
    declare @unitPrice decimal(10, 2);
    select @customerId = @orderXml.value('/order[1]/customer[1]/@id', 'int');
    select @totalPrice = @orderXml.value('/order[1]/totalPrice[1]', 'decimal(10,2)');
    --
    -- validation steps
    --
    -- validate customer
    execute ValidateCustomer @customerId;
    -- build temp table with the orderitems
    create table #orderItems (
        productId int,
        quantity int,
        unitPrice decimal(10, 2)
    );
    -- fill temp table with order items
    insert into #orderItems (productId, quantity, unitPrice)
    select nref.value('product[1]/@id', 'int'), nref.value('quantity[1]', 'int'), nref.value('unitPrice[1]', 'decimal(10,2)')
    from @orderXml.nodes('/order/items/item') as R(nref);
    -- validate that we have all products
    declare @productAvailableCount int;
    declare @itemsCount int;
    select @productAvailableCount = count(*) from #orderItems i, PRODUCT p where p.id = i.productId;
    select @itemsCount = count(*) from #orderItems;
    if @productAvailableCount <> @itemsCount
    begin
        select @e = 'Invalid products in the order. Only ' + cast(@productAvailableCount as varchar(10)) +
            '/' + cast(@itemsCount as varchar(10)) + ' found.';
        RAISERROR(@e, 16, 1);
    end
    --
    -- validation done, now create the order in db.
    --
    -- create orders
    insert into ORDERS (customerId, totalPrice, createdWhen)
    values (@customerId, @totalPrice, CURRENT_TIMESTAMP);
    select @ecode = @@error;
    if @ecode <> 0
    begin
        select @e = 'Could not add orderitem: ' + ERROR_MESSAGE();
        RAISERROR(@e, 16, 1);
    end
    -- get the orderid of the order
    select @orderId = @@IDENTITY;
    -- move the orderitems from temp table into orderitems
    insert into ORDERITEMS (orderId, productId, quantity, unitPrice)
    select @orderId, productId, quantity, unitPrice from #orderItems;
    -- so far so good, commit changes
    commit transaction;
    select @orderId, NULL;
end try
begin catch
    rollback transaction;
    select NULL, ERROR_MESSAGE()
end catch