USE [eRaceClub]
GO
/****** Object:  StoredProcedure [dbo].[LookupInvoiceData]    Script Date: 02/15/2008 17:54:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[LookupInvoiceData]
(@InvoiceInput as INT)
AS
  BEGIN
    SELECT	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.GST, 
			Invoice.SubTotal,
			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 = @InvoiceInput   
  END
RETURN