?
Solved

How to Execute a stored procedure from within a stored procedure

Posted on 2011-02-21
10
Medium Priority
?
799 Views
Last Modified: 2012-08-13

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.
0
Comment
Question by:AkAlan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
10 Comments
 
LVL 41

Expert Comment

by:Sharath
ID: 34947533
I don't know the code of your spTotalItemsCost which gives you the same result.If it gives you the total count, then why do you want another proc.
Anyway, you can create a proc like this
CREATE PROC SPTOTAL 
           @OrderID INT 
AS 
  SELECT OrderID,Name,OrderDate,(SELECT SUM(OrderAmount) 
                                   FROM Items i 
                                  WHERE i.OrderID = co.OrderID) AS TotalItemsCost 
    FROM CustomerOrders co
   WHERE OrderID = @OrderID

Open in new window

In fact, if you are working on SQL SERVER 2008, you can try like this.
CREATE PROC SPTOTAL 
           @OrderID INT 
AS 
  SELECT OrderID,Name,OrderDate,SUM(OrderAmount) 
                                  OVER(PARTITION BY OrderID ) AS TotalItemsCost 
    FROM CustomerOrders 
   WHERE OrderID = @OrderID

Open in new window

0
 
LVL 6

Author Comment

by:AkAlan
ID: 34947591
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.

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

Open in new window


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
0
 
LVL 41

Expert Comment

by:Sharath
ID: 34947759
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') 

Open in new window

 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

Open in new window

0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 41

Expert Comment

by:Sharath
ID: 34947761
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

Open in new window


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

Open in new window

0
 
LVL 6

Author Comment

by:AkAlan
ID: 34947960
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.
0
 
LVL 41

Accepted Solution

by:
Sharath earned 2000 total points
ID: 34947997
Thats my bad. create the function like this.
CREATE FUNCTION dbo.FN_TOTAL 
               (@OANumber INT) 
RETURNS MONEY 
AS 
  BEGIN 
  declare @TotalAmount money
    SELECT @TotalAmount = 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') 
  return @TotalAmount
  END

Open in new window

0
 
LVL 6

Author Comment

by:AkAlan
ID: 34948174
Perfect! Exactly what I needed. Just one last question. Why the function and not a sproc?
0
 
LVL 6

Author Closing Comment

by:AkAlan
ID: 34948176
Thanks for helping, you really saved me a lot of time today.
0
 
LVL 41

Expert Comment

by:Sharath
ID: 34948195
Glad I could help you.
0
 
LVL 41

Expert Comment

by:Sharath
ID: 34948407
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.
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question