locked
How to handle multiple result sets return from Stored Procedure RRS feed

  • Question

  • Hi all,

     

        I want to know how to handle multiple result sets return from Stored Procedure?  I know one way is to insert the result sets into the table, but the limitation is the result sets must have the same data structure.  If the result sets have different data structure, how can I handle it.

     

    Thanks,

    Wednesday, August 22, 2007 5:22 PM

Answers

  • Well, I've been playing around with it a little and so far I can not find a way...

    The only way I've gotten something close to this to work is:

    Code Snippet

    create procedure testing
    as

    select top 5 * into ##tempsup from table1

    select top 6 * into ##tempond from table2



    Code Snippet

    Create procedure testing2
    as

    exec testing

    select * from ##tempsup

    select * from ##tempond


    Wednesday, August 22, 2007 7:19 PM

All replies

  • Are you able to update the proc or do you have to find a way to use it the way it is?
    Wednesday, August 22, 2007 6:18 PM
  •  

    Well in VB I usually use rs.nextrecordset when I have a single SP returning 2 resultsets. I'm not sure what language your using to use the data returned back from your SP. You can have different data structures as well, but your calling code has to handle the different column/field names.

     

    Wednesday, August 22, 2007 6:42 PM
  • I am using TSQL to handle the result set

    Wednesday, August 22, 2007 7:02 PM
  • Well, I've been playing around with it a little and so far I can not find a way...

    The only way I've gotten something close to this to work is:

    Code Snippet

    create procedure testing
    as

    select top 5 * into ##tempsup from table1

    select top 6 * into ##tempond from table2



    Code Snippet

    Create procedure testing2
    as

    exec testing

    select * from ##tempsup

    select * from ##tempond


    Wednesday, August 22, 2007 7:19 PM