I'm trying to create a sales invoice with multiple table. Here is my stored procedure.
ALTER PROCEDURE CreateInvoice
(
@CustomerID nvarchar(50),
@JobOrderID nvarchar(50)
)
AS
SELECT Invoice.JobOrderID, ProductOrders.StyleNumber, ProductOrders.Sizes, ProductOrders.OrderedQuantity, ProductOrders.QuantityDelivered, ProductOrders.UnitCost,
ProductOrders.SubTotal, Customer.BusinessName, CustomerAddress.StreetNumber, CustomerAddress.City, CustomerAddress.Province, CustomerAddress.ZipCode,
Invoice.CustomerID
FROM Invoice INNER JOIN
ProductOrders ON Invoice.JobOrderID = ProductOrders.JobOrderID INNER JOIN
Customer ON Invoice.CustomerID = Customer.CustomerID INNER JOIN
CustomerAddress ON Invoice.CustomerID = CustomerAddress.CustomerID
WHERE (Invoice.JobOrderID = @JobOrderID) AND (Invoice.CustomerID = @CustomerID) AND (CustomerAddress.Type = N'Shipping')
RETURN