Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2002
    Posts
    45

    Unanswered: selecting sum of 3 stored procs.

    I have written a huge sql statement and broke it into 3 stored procs for ease of use and testing.

    Can i create 1 stored proc that sums the results of the other 3?

    I have tried:

    select sum(commissions) from
    (exec proc1 'params'
    exec proc2 'params'
    exec proc3 'params'
    )

    an ideas?

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Best I can tell you is to have a procedure like this:

    create proc (all params)
    create table #temp
    (commissions decimal(n,p))

    insert into #temp
    exec proc1 params1

    insert into #temp
    exec proc2 params2

    insert into #temp
    exec proc3 params3

    select sum (commissions)
    from #temp

    drop table #temp

  3. #3
    Join Date
    Dec 2002
    Posts
    45
    The 3 procs are filled with derived tables and not temp tables for speed as it is. Using a temp table at this point would loose my cause.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Good point. In fact, over the weekend, I figured why not return the sum as a return value and do something like:

    declare @num1 decimal(n,p)
    declare @num2 decimal(n,p)
    declare @num3 decimal(n,p)

    exec @num1 = proc1 params
    exec @num2 = proc2 params
    exec @num3 = proc3 params

    select @num1 + @num2 + @num3


    If only integers can be retuned, then you may have to fudge them into integers by multiplying all of them by a factor of 10. Hope this helps.

  5. #5
    Join Date
    Sep 2003
    Posts
    522
    you can also use output parameters, this way you won't have to do any multiplications.

Posting Permissions

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