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