I have a sproc (spOrderSummary) that returns a summary of Customer Orders. Each Customer Order can have one or more Items. I want one of the columns in spOrderSummary to be a total cost of all the Items for each Customer Order.
I already have a sproc (spTotalItemsCost) that takes in the OrderNumber as a parameter and returns the total cost of all Items in the Customer Order.
I'm currently using a Select statement but it is pretty involved and I was hoping to make it cleaner by executing spTotalItemsCost from within spOrderSummary passing it the OrderNumber for every record.
Here is what I'm doing now (simplified for this thread)
Select OrderID, Name, OrderDate, (Select SUM(OrderAmount) FROM Items i WHERE i.OrderID = co.OrderID) as TotalItemsCost
FROM CustomerOrders co
How could I replace the Select statemnet with a sproc and pass it the OrderID for every record?
Thanks for any help.