AkAlan
asked on
How to Execute a stored procedure from within a stored procedure
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.
ASKER
I am already doing what you suggest but as I had said, I simplified my example just for the purposes of this post. I actually need totals from three different sets of tables and the code is pretty involved. I have three sprocs created that return the totals for me I just want this summary sproc to use the sprocs already created to return the totals I need. I will post an example of just one of the total costs I need to give you an idea.
As you can see it isn't as simple to get what I need. I'm just looking to make this sproc more readable. Thanks for any help
ALTER PROCEDURE [dbo].[spSelectENG_OverAndAboveSummary]
AS
SELECT
OANumber,
ProjectDescription,
EngineerPOC,
ProposalPrice,
ApprovedPrice,
PercentComplete,
-- this next few lines is what I want to replace with a sproc. I also have to get totals from two other places
cast((SELECT SUM(PoLn.PO_LN_TOT_AMT)
FROM CpLink.dbo.PO_LN PoLn
JOIN
CpLink.dbo.PO_LN_ACCT PoLnAcct ON PoLnAcct.PO_ID =PoLn.PO_ID
AND PoLnAcct.PO_RLSE_NO =PoLn.PO_RLSE_NO
AND PoLnAcct.PO_LN_KEY =PoLn.PO_LN_KEY
WHERE CASE WHEN CAST(SUBSTRING(PoLnAcct.PROJ_ID, 10, 2) AS Int) < 5
THEN '0'
ELSE ''
END + CAST(CAST(SUBSTRING(PoLnAcct.PROJ_ID, 10, 2)
AS Int) + 5 AS varchar(2)) + '-' + RIGHT(PoLnAcct.PROJ_ID, 2) LIKE oap.OANumber
AND
(RIGHT(PoLnAcct.ORG_ID, 3) = '130')
AND (SUBSTRING(PoLnAcct.PROJ_ID, 10, 1) <> 'X') ) as money)
as TotalPurchases
FROM dbo.FAC_OverAndAboveProjects oap
As you can see it isn't as simple to get what I need. I'm just looking to make this sproc more readable. Thanks for any help
Instead of an SP, create a function and use that function.
CREATE FUNCTION dbo.FN_TOTAL
(@OANumber INT)
RETURNS MONEY
AS
BEGIN
SELECT CONVERT(MONEY,SUM(PoLn.PO_LN_TOT_AMT))
FROM CpLink.dbo.PO_LN PoLn
JOIN CpLink.dbo.PO_LN_ACCT PoLnAcct
ON PoLnAcct.PO_ID = PoLn.PO_ID
AND PoLnAcct.PO_RLSE_NO = PoLn.PO_RLSE_NO
AND PoLnAcct.PO_LN_KEY = PoLn.PO_LN_KEY
WHERE CASE
WHEN CAST(SUBSTRING(PoLnAcct.PROJ_ID,10,2) AS INT) < 5 THEN '0'
ELSE ''
END + CAST(CAST(SUBSTRING(PoLnAcct.PROJ_ID,10,2) AS INT) + 5 AS VARCHAR(2)) + '-' + RIGHT(PoLnAcct.PROJ_ID,2) LIKE oap.OANumber
AND (RIGHT(PoLnAcct.ORG_ID,3) = '130')
AND (SUBSTRING(PoLnAcct.PROJ_ID,10,1) <> 'X')
ENDALTER PROCEDURE [dbo].[SPSELECTENG_OVERANDABOVESUMMARY]
AS
SELECT OANumber,
ProjectDescription,
EngineerPOC,
ProposalPrice,
ApprovedPrice,
PercentComplete,
-- this next few lines is what I want to replace with a sproc. I also have to get totals from two other places
dbo.FN_TOTAL(oap.OANumber) AS TotalPurchases
FROM dbo.FAC_OverAndAboveProjects oap
Missed END
CREATE FUNCTION dbo.FN_TOTAL
(@OANumber INT)
RETURNS MONEY
AS
BEGIN
SELECT CONVERT(MONEY,SUM(PoLn.PO_LN_TOT_AMT))
FROM CpLink.dbo.PO_LN PoLn
JOIN CpLink.dbo.PO_LN_ACCT PoLnAcct
ON PoLnAcct.PO_ID = PoLn.PO_ID
AND PoLnAcct.PO_RLSE_NO = PoLn.PO_RLSE_NO
AND PoLnAcct.PO_LN_KEY = PoLn.PO_LN_KEY
WHERE CASE
WHEN CAST(SUBSTRING(PoLnAcct.PROJ_ID,10,2) AS INT) < 5 THEN '0'
ELSE ''
END + CAST(CAST(SUBSTRING(PoLnAcct.PROJ_ID,10,2) AS INT) + 5 AS VARCHAR(2)) + '-' + RIGHT(PoLnAcct.PROJ_ID,2) LIKE oap.OANumber
AND (RIGHT(PoLnAcct.ORG_ID,3) = '130')
AND (SUBSTRING(PoLnAcct.PROJ_ID,10,1) <> 'X')
END
ALTER PROCEDURE [dbo].[SPSELECTENG_OVERANDABOVESUMMARY]
AS
SELECT OANumber,
ProjectDescription,
EngineerPOC,
ProposalPrice,
ApprovedPrice,
PercentComplete,
-- this next few lines is what I want to replace with a sproc. I also have to get totals from two other places
dbo.FN_TOTAL(oap.OANumber) AS TotalPurchases
FROM dbo.FAC_OverAndAboveProjects oap
ASKER
Thanks, Looks like a good solution. I have never created functions, I get errors when trying to create this function:
Msg 444, Level 16, State 2, Procedure fnENG_O_A_TotalPurchaces, Line 7
Select statements included within a function cannot return data to a client.
Msg 455, Level 16, State 2, Procedure fnENG_O_A_TotalPurchaces, Line 7
The last statement included within a function must be a return statement.
Msg 444, Level 16, State 2, Procedure fnENG_O_A_TotalPurchaces, Line 7
Select statements included within a function cannot return data to a client.
Msg 455, Level 16, State 2, Procedure fnENG_O_A_TotalPurchaces, Line 7
The last statement included within a function must be a return statement.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Perfect! Exactly what I needed. Just one last question. Why the function and not a sproc?
ASKER
Thanks for helping, you really saved me a lot of time today.
Glad I could help you.
You can use the scalar value function in your query (in SELECT clause for your requirement) where as SP cannot be used in a SELECT query. If you want to go with SP, you need to loop over your table (either a cursor or a while/for loop) and calculate the Total Amount.
Anyway, you can create a proc like this
Open in new window
In fact, if you are working on SQL SERVER 2008, you can try like this.Open in new window