January 23, 2015 at 9:23 am
Hi All,
I am having a limited knowledge in SQL. Let me explain my scenario in detail. I have to write a Stired Procedure with the following functionality.
Write a simple select query say (Select * from tableA) result is
ProdName ProdID
----------------------
ProdA 1
ProdB 2
ProdC 3
ProdD 4
Now with the above result, On every record I have to fire a query
Select SUM(sale), SUM(scrap), SUM(Production) from tableB where ProdID= ["ProdID from above query"]
How to write this query in a Stored Procedure so that I can get the required SUM columns for all the ProdID's from first query?
Regards,
Girish
January 23, 2015 at 9:35 am
girish.nehte-1109699 (1/23/2015)
Hi All,I am having a limited knowledge in SQL. Let me explain my scenario in detail. I have to write a Stired Procedure with the following functionality.
Write a simple select query say (Select * from tableA) result is
ProdName ProdID
----------------------
ProdA 1
ProdB 2
ProdC 3
ProdD 4
Now with the above result, On every record I have to fire a query
Select SUM(sale), SUM(scrap), SUM(Production) from tableB where ProdID= ["ProdID from above query"]
How to write this query in a Stored Procedure so that I can get the required SUM columns for all the ProdID's from first query?
Regards,
Girish
What does your procedure have to produce? Is it inserting records in a new table, or returning data to variables, or returning the results of one select, or returning the results of multiple selects?
Based on your goal it may change what the optimal approach is to doing it. The basic functionality you describe would be a cursor, which would let you loop through the first query, running the second query inside.
But depending on what your actual goal is, there may be a vastly more efficient way of doing this.
January 23, 2015 at 10:15 am
girish.nehte-1109699 (1/23/2015)
Hi All,I am having a limited knowledge in SQL. Let me explain my scenario in detail. I have to write a Stired Procedure with the following functionality.
Write a simple select query say (Select * from tableA) result is
ProdName ProdID
----------------------
ProdA 1
ProdB 2
ProdC 3
ProdD 4
Now with the above result, On every record I have to fire a query
Select SUM(sale), SUM(scrap), SUM(Production) from tableB where ProdID= ["ProdID from above query"]
How to write this query in a Stored Procedure so that I can get the required SUM columns for all the ProdID's from first query?
Regards,
Girish
This type of looping is almost always unnecessary. We REALLY need create table statement(s), sample INSERT data and expected results to truly help, but what about this query, which gives you all data in a single, set-based operation:
SELECT ProdID, SUM(sale), SUM(scrap), SUM(Production)
FROM tableB
WHERE ProdID IN (SELECT ProdID from tableA)
GROUP BY ProdID
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply