Create proc [dbo].[LookupMemberInvoiceData]
(@Invoice# as int)
as
Begin
SET NOCOUNT ON;
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.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