SET QUOTED_IDENTIFIER OFF
go

use wroxdb
go
--
--   Name:  validate_order
--
--   Description: check that the sub-totals all match
--
--
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'validate_order')
BEGIN
    DROP PROCEDURE validate_order
END
GO
 
PRINT 'validate_order'
GO
CREATE PROCEDURE validate_order @order int
AS
DECLARE @pay_sum money
DECLARE @line_sum money
DECLARE @shipping money

	SELECT @shipping = shipping_charge
	FROM orders
	WHERE order_no = @order

	SELECT @line_sum = SUM(qty * unit_price)
	FROM lines
	WHERE order_no = @order

	
	SELECT @pay_sum = SUM(amount)
	FROM payments
	WHERE order_no = @order

	IF @pay_sum <> (@shipping + @line_sum)
		RAISERROR 99901 'Payments and Line totals do not match'
	ELSE
	BEGIN
		PRINT 'Order ' + convert(varchar,@order) + ' validated'
		RETURN 10
	END

GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'validate_order')
   PRINT  '>>> Created procedure validate_order <<<'
GO

-- test
-- exec validate_order 1000

