use wroxdb
go
--
--   Name:  get_ord_payments
--
--   Description: get payment records for one order
--
--
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'get_ord_payments')
BEGIN
    DROP PROCEDURE get_ord_payments
END
GO
 
PRINT 'get_ord_payments'
GO
CREATE PROCEDURE get_ord_payments @ord int
as

	SELECT '<payments>'
	
	SELECT xact='-', order_no, pmt_no, p.pmt_type, descr,
		CONVERT(varchar(10),amount) as amount
	FROM payments p INNER JOIN pmt_types t
	ON p.pmt_type = t.pmt_type
	WHERE order_no = @ord
	FOR XML RAW
	
	SELECT '</payments>'

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

-- test
-- get_ord_payments 1000
