Post reply

(n)th resultset of a procedure

  • July 18, 2006 at 12:39 am

    #153090

    Hi Buddies!

    Hope everything goes well

    I have faced with a question as I stepped into office this morning. A developer needs to retrieve the second resultset of a previously created procedure. I created a repro like this:

    -- Creating test procedure
    CREATE
     PROCEDURE sp_test
    AS
    SELECT 55 -- First resultset, int typeSELECT 'xx' -- Second resultset, char type
    GO
     
    -- Create temp table to insert results
    CREATE
     TABLE #tblTest(
    a 
    INT,
    b VARCHAR
    (5))
     
    -- insert the results into temp table 
    INSERT
     INTO #tblTest
    EXEC
     sp_test
     
    -- select results 
    SELECT
     * FROM #tblTest

    If the column type of the second resultset was int as the first one then without any problem all the resultsets (which are identical) would be inserted into the same temp table (#tblTest in this example). But hence they are different resultsets then they won't go into the same temp table.

    Now the question comes: how can I retrieve the second resultset of this sp? (Assuming that I cannot change it)

    Thanks in advance for all answers.

  • July 18, 2006 at 2:12 am

    #649724

    If you change your stored procedure as below, it should work.

    John

    -- Creating test procedure

    CREATE

    PROCEDURE sp_test

    AS

    SELECT 55 AS a, 'xx'  AS b

    GO

  • July 18, 2006 at 2:18 am

    #649725

    Thanks John,

    I wish I could do that but the stored procedure is compiled by another developer and cannot be changed for this purpose.

    Additionally the resutset structure is not just one column. Each resultset has different sets of columns.

     

  • July 18, 2006 at 2:43 am

    #649727

    If you can't change the stored procedure then I think your only option is to have the client side manipulate the result sets for you.

    John

  • July 18, 2006 at 5:21 am

    #649748

    Well, with your example you can do this...

    CREATE PROCEDURE sp_test

    AS

    SELECT 55

    SELECT 'xx'

    GO

    CREATE TABLE #tblTest (v sql_variant)

    INSERT INTO #tblTest EXEC sp_test

    SELECT v, SQL_VARIANT_PROPERTY (v, 'BaseType') as 'BaseType' FROM #tblTest

    DROP TABLE #tblTest

    DROP PROCEDURE sp_test

    /*results

    v           BaseType   

    ----------- ------------

    55          int

    xx          varchar

    */

    However, if your resultsets have a different number of columns (this is the issue - not the data type), I also believe you have to go client-side.

     

    Ryan Randall

    Solutions are easy. Understanding the problem, now, that's the hard part.

  • July 18, 2006 at 7:20 am

    #649772

    Thanks Ryan!

    It a good idea to use sql_variant indeed. I suggested the developer to use sql_variant and she stated that her resultsets has different number of columns and I suggeted her to go client side

    Thank you all for your efforts.

  • July 18, 2006 at 9:53 am

    #649850

    Or redesign that sp into multiple sp(s) and let that developer choose the lower level one. In other words, modularize the code

     


    * Noel

Viewing 7 posts - 1 through 6 (of 6 total)

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