Post reply

Using the result set of a stored procedure...?

  • January 14, 2008 at 7:47 pm

    #69700

    I have a procedure whose last line is a select statement. Hence this proc gives me a result set in the end. Can I use this result set for further querying...(post procedure execution processing)?

    I mean something like:

    select * from (exec usp_myResultSetYeildingProc).

  • January 14, 2008 at 8:10 pm

    #767962

    CREATE TABLE ResultSetYeildingProc (...)

    INSERT INTO ResultSetYeildingProc

    exec usp_myResultSetYeildingProc

    SELECT * FROM ResultSetYeildingProc

    _____________
    Code for TallyGenerator

  • January 15, 2008 at 1:37 am

    #768000

    Sergiy, Thanks for the helping hand to OP. I have a question to add:

    How can I create a table to hold the recordset without knowing its schema?

    Regards,

    goodguy

    Experience is a bad teacher whose exams precede its lessons

  • January 15, 2008 at 9:02 am

    #768205

    Is there no way to peek at the code of the SP you are calling and determine that? It will be the datatypes of all of the columns that are being returned by the sp, which in turn are the datatypes of the original source columns.

    Anyone know if there are some SPs out there that will return metadata of a stored procedure?

  • January 15, 2008 at 1:47 pm

    #768317

    Stored procedure may return results via output parameters, output parameters+recordset, multiple recordsets, temp table(s), etc.

    Metadata for which of these options you want to find?

    _____________
    Code for TallyGenerator

  • January 15, 2008 at 2:23 pm

    #768337

    I was just thinking of a plain vanilla stored procedure that returns a single result set. When I develop client side applications, or even SQL Server Reporting Services, some code runs to determine the data types of all of the return columns, and I was wondering if there was a pure SQL Server way to do this. Whenever I have had to do this before, I just look at the code of the procedure I'm interested in, and do it by hand.

  • January 18, 2008 at 2:12 pm

    #769887

    Ok here is a scenario. What if based on some condition your procedure displayed two or more different kinds of result sets? Is there a way to programmatically determine the table structure of the result set?

  • January 20, 2008 at 1:10 pm

    #770011

    Arun T Jayapal (1/18/2008)

    Ok here is a scenario. What if based on some condition your procedure displayed two or more different kinds of result sets? Is there a way to programmatically determine the table structure of the result set?

    There is the way, but not sure is it programmatic.

    Fetch the document about this SP and find out.

    _____________
    Code for TallyGenerator

  • January 20, 2008 at 1:49 pm

    #770012

    Such things are best done in client side code. It sounds like you might be making your stored procedure too generic.

  • February 5, 2008 at 11:34 am

    #775264

    This is the best answer I have found on the subject.

    http://www.sommarskog.se/share_data.html

    Of that the most generic method was the following.

    sp_addlinkedserver @server = 'LOCALSERVER', @srvproduct = '',

    @provider = 'SQLOLEDB', @datasrc = @@servername

    SELECT *

    into #tmpWho

    FROM OPENQUERY(LOCALSERVER, 'EXEC sp_who')

    Note however, that this method is very costly. If anyone else has some ideas on how to do this, I would also appreciate it.

    At the very least to protect against changes to the schema by just returning the columns I need rather than having to match the columns exactly.

  • May 13, 2008 at 3:43 pm

    #814842

    Honestly....there really is a simple way to accomplish what he is asking for. Some of thee suggestions are overly complicated...

    I'll see if I cant' find a simpler way than creating temp tables and writing client side code...

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply