--
--   Name:  get_ord_lines
--
--   Description:    
--
--
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'get_ord_lines')
BEGIN
    DROP PROCEDURE get_ord_lines
END
GO
 
PRINT 'get_ord_lines'
GO
CREATE PROCEDURE get_ord_lines @ord int
AS
	select '<lines>'

	select xact='-', order_no, line_no, l.prod_id, descr, qty, 
		CONVERT(varchar(10),unit_price) as unit_price, 
		CONVERT(varchar(10),unit_cost) as unit_cost,
		CONVERT(varchar(10),commission) as commission
	FROM lines l INNER JOIN products p
	ON l.prod_id=p.prod_id
	WHERE order_no = @ord
	FOR XML RAW

	select '</lines>'
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'get_ord_lines')
   PRINT  '>>> Created procedure get_ord_lines <<<'
GO

-- test
-- get_ord_lines 1000
