Stored Procedure Using Nested Queries
Posted on 2004-11-22
I’m trying to create a stored procedure that is based off of tables that track Bill of Materials (Project Demand), Orders (Headers and Details) and Shipments (Headers and Details). What I’m trying to do is return a record set that show all SKU & Description and the quantity drawn, and then shows any summed quantities that have been ordered or shipped. I initially created this as separate views that I then linked by Store Number and SKU, but it was running very slowly. What I’m trying to do now is create a store procedure that limits each of the record sets by store, and then joins them by SKU, but I’m having trouble with nested query syntax.
The questions I have are, is this the right approach, and if so, how do I link the sub-queries by SKU number?
Here’s is what I have so far. It doesn’t include the shipment tables yet, since I’m still trying to get the orders portion correct. Also, a store number variable will replace the actual store number value I’m using in the example. Any help would be appreciated!
Select dbo.ProjectDemand.ProjectNumber, dbo.ProjectMaster.StoreNumber, dbo.ProjectDemand.SKUNumber,
dbo.SKUMaster.SKUDescription As ItemDescription, dbo.ProjectDemand.Quantity As QtyDrawn,
(Select dbo.OrderDetails.SKUNumber, Sum(dbo.OrderDetails.QtyOrdered) As QtyOrdered
From dbo.OrderDetails Inner Join
dbo.OrderHeaders ON dbo.OrderDetails.OrderID = dbo.OrderHeaders.OrderID
Where (dbo.OrderHeaders.StoreNumber = 9412)
Group By dbo.OrderDetails.SKUNumber
dbo.ProjectDemand Inner Join dbo.ProjectMaster On dbo.ProjectDemand.ProjectNumber = dbo.ProjectMaster.ProjectNumber
Left Outer Join dbo.SKUMaster On dbo.ProjectDemand.SKUNumber = dbo.SKUMaster.SKUNumber
Left Outer Join dbo.ProjectDemand On dbo.ProjectDemand.SKUNumber = OrderSummary.SKUNumber
Where dbo.ProjectMaster.StoreNumber = 9412