Home Forums SQL Server 2012 SQL Server 2012 - T-SQL Write a loop on result of first query inside a Stored Procedure RE: Write a loop on result of first query inside a Stored Procedure

  • January 23, 2015 at 10:15 am

    #1772732

    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