ALTER proc [dbo].[LookupMemberInvoiceData]
(@Invoice# as int)
as
Begin

	SET NOCOUNT ON;

	SELECT	Distinct 
			Case 
				when member.memberId is null then '0'
				Else member.memberId
			end as memberId,
			Case 
				when member.memberId is null then 'Unknown'
				Else member.lastname + ', ' + member.firstname
			end as name,
			Case 
				when member.memberId is null then ''
				Else member.address
			end as address,
			Case 
				when member.memberId is null then ''
				Else member.city
			end as city,
			Case 
				when member.memberId is null then ''
				Else member.postalcode
			end as postalcode,
			invoice.invoicenumber,
			invoice.invoicedate, paymenttype.description as paymenttype, 
			invoice.subtotal, invoice.gst, invoice.total, invoicedetail.productid, 
			product.description, invoicedetail.quantity, invoicedetail.price
 
	from	invoice left outer join member on invoice.memberid = member.memberid
			inner join paymenttype on invoice.paymenttypeid = paymenttype.paymenttypeid
			inner join invoicedetail on invoice.invoicenumber = invoicedetail.invoicenumber
			inner join product on invoicedetail.productid = product.productid
	
	where	invoice.invoicenumber = @Invoice#
		
End