Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2016
    Posts
    19

    Unanswered: calling stored procedure in loop and selecting from results

    Hello,

    I have a stored procedure that takes a project ID as a parameter and returns a list of results on that project. Now what I'd like to do is create another stored procedure that takes a list of project IDs as a parameter, and cycles through each one calling the first stored procedure. The second stored procedure needs to SELECT from the cumulative results of all the first stored procedure calls.

    For example, the first stored procedure has these two columns (among others): CATEGORY, and TOTAL_CREDITS.

    If for project 1, it returned these results:

    CATEGORY, TOTAL_CREDITS
    BPCS, 3
    MEC, 4

    and if for project 2, it returned these results:

    CATEGORY, TOTAL_CREDITS
    BPCS, 9
    MEC, 15

    The second stored procedure needs to select the sum of TOTAL_CREDITS for each CATEGORY:

    CATEGORY, TOTAL_CREDITS
    BPCS, 12
    MEC, 19

    How can this be done?

    Thanks.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,862
    Provided Answers: 17
    You can create a temp table to collect the results. I am not certain of the exact syntax, but something like:

    Code:
    create table #temp
    (category char(4),
     credits int)
    
    insert into #temp
    exec proc1
    This is of course a slightly dangerous construct, as the nesting of procedures (similar to the nesting of views) could get rather cumbersome to maintain, and introduce all sorts of performance problems if the procedures do other things beside return these results. In a simple case, it should not be too bad, but if you find yourself nesting 4 or 5 procedures, you may want to step back, and re-evaluate.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,445
    Provided Answers: 12
    If you're thinking in loops; chances are you're approaching the solution in the wrong way.
    You can probably write a single procedure that will perform the actions you require, in a set-based manner, by grouping by each project!

    Pseudo-code:
    Code:
    SELECT project
         , CATEGORY
         , Sum(credits) AS TOTAL_CREDITS
    FROM   your_table
    WHERE  project IN (SELECT project FROM @projects_parameter)
    GROUP
        BY project
         , CATEGORY
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •