IF OBJECT_ID('usp_NewOrder', 'P') IS NOT NULL
DROP PROCEDURE usp_NewOrder;
GO

CREATE PROCEDURE usp_NewOrder
	@id_client INT,
	@product_number CHAR(20),
	@qty INT
AS
	DECLARE @err_message CHAR(200) 
	BEGIN TRANSACTION
	BEGIN
		BEGIN TRY
			DECLARE @qty_in_stock INT
			DECLARE @final_qty INT
			SELECT @qty_in_stock = (SELECT nbr_in_stock
									FROM Product_Inventory
									WHERE product_number = @Product_number)
			FROM Product_Inventory
			SELECT @final_qty = (@qty_in_stock - @qty)
			INSERT INTO Inventory_Orders(id_client, product_number, qty, order_date)
			VALUES(	@id_client,	@product_number, @final_qty, GETDATE()	)
		END TRY

	BEGIN CATCH
			SELECT @err_message = ERROR_MESSAGE()
			IF @@TRANCOUNT > 0
				ROLLBACK TRANSACTION;
			BEGIN
				EXECUTE usp_Order_Error @id_client, @product_number, @qty,  @qty_in_stock, @err_message
			END;
	END CATCH
	IF @@TRANCOUNT > 0
   COMMIT TRANSACTION
END
