locked
Reading Stored Procedure Recordsets RRS feed

  • Question

  • Hi
    How can I read recordsets of a stored procedure in SQL. Specially if the stored procedure returns multiple recordsets like sp_spaceused. I'm using SQL Server 2005.
    Regards
    Saturday, January 2, 2010 4:16 AM

Answers

  • In the SQL Server Denali we can utilize multiple result sets. I did a very quick google search and I didn't find BOL links explaining exact syntax (didn't search hard enough), but as usual I found a good blog by Pinal Dave on this topic

    http://blog.sqlauthority.com/2011/04/29/sql-server-denali-executing-stored-procedure-with-result-sets-2/


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Marked as answer by Kalman Toth Friday, May 6, 2011 8:56 AM
    Sunday, May 1, 2011 10:10 PM
  • Do you mean in your application?
    Check out this link


    Abdallah El-Chal, PMP, ITIL, MCTS
    • Marked as answer by Kalman Toth Friday, May 6, 2011 8:55 AM
    Saturday, January 2, 2010 4:30 AM
  • UDF Returns single Table only,

    Stored procedure can return multiple recordsets

    Create Procedure Pr_GetCustomerDetails()
    As
    Begin
    Select * from Customer
    Select * from CustomerBalance
    Select * from CustomerItemPosting
    End



    Happy Coding, RDRaja
    • Marked as answer by Kalman Toth Friday, May 6, 2011 8:55 AM
    Saturday, January 2, 2010 10:11 AM
  • No. In in SQL. Suppose I want to read it in an UDF(user defined function)

    Not easy! Usually you get double sliders on the right hand side of the query result window, indicating multiple result sets.

    It is best to avoid it in stored procedures.

    OPENQUERY returns the first dataset only. SQLCMD can catch it all results with the -o option. Demo follows.
    -- T-SQL SPROC with multiple result sets (tables) return
    -- OPENQUERY returns first
    -- SQLCMD catches all
    USE tempdb;
    GO
    CREATE PROC sprocMultipleResultSets
    AS
    BEGIN
    SELECT ProductName=Name, ListPrice
    FROM AdventureWorks2008.Production.Product
    SELECT SalesOrderID, OrderDate, TotalDue
    FROM AdventureWorks2008.Sales.SalesOrderHeader
    END
    GO
    
    -- First result set returned
    SELECT * FROM OPENQUERY(SERVERNAME, 'exec tempdb.dbo.sprocMultipleResultSets')
    GO
    /*
    ProductName	ListPrice
    ....
    Mountain-100 Silver, 44	3399.99
    Mountain-100 Silver, 48	3399.99
    Mountain-100 Black, 38	3374.99
    Mountain-100 Black, 42	3374.99
    ....
    */
    -- At command prompt or with xp_cmdshell
    /*
    SQLCMD -q"exec tempdb.dbo.sprocMultipleResultSets" -o F:\temp\SQLCMDout1.txt
    */
    /*
    ......
    Road-750 Black, 44                                              539.9900
    Road-750 Black, 48                                              539.9900
    Road-750 Black, 52                                              539.9900
    
    (504 rows affected)
    SalesOrderID OrderDate               TotalDue             
    ------------ ----------------------- ---------------------
           43659 2001-07-01 00:00:00.000            27231.5495
           43660 2001-07-01 00:00:00.000             1716.1794
           43661 2001-07-01 00:00:00.000            43561.4424
    ......
    */

    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    • Marked as answer by Kalman Toth Friday, May 6, 2011 8:53 AM
    • Edited by Kalman Toth Thursday, September 27, 2012 5:02 PM
    Tuesday, January 5, 2010 6:01 PM
  • Kalman,

    It seems to be The OP is expecting an sql equivalent of DataSet(in .net)

    suppose, when i call sp_spaceused from the front-end, it will give me all the results into a dataset, then i can retrieve each datatable and can do the manipulations....


    in the same way, is there any thing in sql, which when i call sp_spaceused, it needs to have all the result sets... then i need to iterate like that....


    in otherwords, in sql, a record set means a table variable which has the set of rows. .. its .net equivalent is a datatable...


    now what is the equivalent of dataset in sql???


    it seems to be This is his question...


    as far as i know, there is nothing like that in sql...
    • Proposed as answer by Naomi N Tuesday, January 5, 2010 6:14 PM
    • Marked as answer by Kalman Toth Friday, May 6, 2011 8:52 AM
    Tuesday, January 5, 2010 6:10 PM

All replies

  • Do you mean in your application?
    Check out this link


    Abdallah El-Chal, PMP, ITIL, MCTS
    • Marked as answer by Kalman Toth Friday, May 6, 2011 8:55 AM
    Saturday, January 2, 2010 4:30 AM
  • No. In in SQL. Suppose I want to read it in an UDF(user defined function)
    Saturday, January 2, 2010 9:58 AM
  • UDF Returns single Table only,

    Stored procedure can return multiple recordsets

    Create Procedure Pr_GetCustomerDetails()
    As
    Begin
    Select * from Customer
    Select * from CustomerBalance
    Select * from CustomerItemPosting
    End



    Happy Coding, RDRaja
    • Marked as answer by Kalman Toth Friday, May 6, 2011 8:55 AM
    Saturday, January 2, 2010 10:11 AM
  • I know the difference between UDF and Sproc! Please read my original question.
    Sunday, January 3, 2010 3:51 AM
  • Where you want to read another stored procedure or client side (C#)??

    Happy Coding, RDRaja
    Sunday, January 3, 2010 5:06 AM
  • You can create temp tables in one stored procedure and select into these temp tables in another stored procedure.

    Other than that I don't see a way to utilize multiple result sets from one SP into another SP. In custom application (.NET for example) it's very simple to utilize multiple result sets (ADO.NET provides a mechanism for this).
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Sunday, January 3, 2010 5:52 AM
  • Hi.
    Please refer to the url below:
    http://support.microsoft.com/default.aspx/kb/176086
    Regards,
    deshly.
    __________________
    RAQ Report: Free Excel-like Web-based Java reporting tool

    Sunday, January 3, 2010 5:31 PM
  • No. In in SQL. Suppose I want to read it in an UDF(user defined function)

    Not easy! Usually you get double sliders on the right hand side of the query result window, indicating multiple result sets.

    It is best to avoid it in stored procedures.

    OPENQUERY returns the first dataset only. SQLCMD can catch it all results with the -o option. Demo follows.
    -- T-SQL SPROC with multiple result sets (tables) return
    -- OPENQUERY returns first
    -- SQLCMD catches all
    USE tempdb;
    GO
    CREATE PROC sprocMultipleResultSets
    AS
    BEGIN
    SELECT ProductName=Name, ListPrice
    FROM AdventureWorks2008.Production.Product
    SELECT SalesOrderID, OrderDate, TotalDue
    FROM AdventureWorks2008.Sales.SalesOrderHeader
    END
    GO
    
    -- First result set returned
    SELECT * FROM OPENQUERY(SERVERNAME, 'exec tempdb.dbo.sprocMultipleResultSets')
    GO
    /*
    ProductName	ListPrice
    ....
    Mountain-100 Silver, 44	3399.99
    Mountain-100 Silver, 48	3399.99
    Mountain-100 Black, 38	3374.99
    Mountain-100 Black, 42	3374.99
    ....
    */
    -- At command prompt or with xp_cmdshell
    /*
    SQLCMD -q"exec tempdb.dbo.sprocMultipleResultSets" -o F:\temp\SQLCMDout1.txt
    */
    /*
    ......
    Road-750 Black, 44                                              539.9900
    Road-750 Black, 48                                              539.9900
    Road-750 Black, 52                                              539.9900
    
    (504 rows affected)
    SalesOrderID OrderDate               TotalDue             
    ------------ ----------------------- ---------------------
           43659 2001-07-01 00:00:00.000            27231.5495
           43660 2001-07-01 00:00:00.000             1716.1794
           43661 2001-07-01 00:00:00.000            43561.4424
    ......
    */

    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    • Marked as answer by Kalman Toth Friday, May 6, 2011 8:53 AM
    • Edited by Kalman Toth Thursday, September 27, 2012 5:02 PM
    Tuesday, January 5, 2010 6:01 PM
  • Kalman,

    It seems to be The OP is expecting an sql equivalent of DataSet(in .net)

    suppose, when i call sp_spaceused from the front-end, it will give me all the results into a dataset, then i can retrieve each datatable and can do the manipulations....


    in the same way, is there any thing in sql, which when i call sp_spaceused, it needs to have all the result sets... then i need to iterate like that....


    in otherwords, in sql, a record set means a table variable which has the set of rows. .. its .net equivalent is a datatable...


    now what is the equivalent of dataset in sql???


    it seems to be This is his question...


    as far as i know, there is nothing like that in sql...
    • Proposed as answer by Naomi N Tuesday, January 5, 2010 6:14 PM
    • Marked as answer by Kalman Toth Friday, May 6, 2011 8:52 AM
    Tuesday, January 5, 2010 6:10 PM
  • OPENQUERY (and similar) and INSERT EXEC can be used to make stored procedure output table like.

    OPENQUERY returns the first result set in table format. INSERT EXEC fails on multiple result sets.

    Only SQLCMD works, the output flat file can be uploaded to a staging table and parsed for the various result sets (not pretty, but works).

    The output structure of sp_spaceused is inherited from Sybase, it is really "ancient".

    Demo follows:

    SELECT * FROM OPENQUERY(SERVERNAME, 'exec AdventureWorks2008.dbo.sp_spaceused')
    /*
    database_name	database_size	unallocated space
    AdventureWorks2008	266.00 MB	45.99 MB
    */
    
    SELECT a.*
    FROM OPENROWSET('SQLNCLI', 'Server=SERVERNAME;Trusted_Connection=yes;',
         'exec AdventureWorks2008.dbo.sp_spaceused') AS a;
    /*
    database_name	database_size	unallocated space
    AdventureWorks2008	266.00 MB	45.99 MB
    */
    
    -- INSERT EXEC demo
    SELECT TOP (0) * 
    INTO #SpaceUsed
    FROM OPENQUERY(SERVERNAME, 'exec AdventureWorks2008.dbo.sp_spaceused')
    GO
    INSERT #SpaceUsed
    exec AdventureWorks2008.dbo.sp_spaceused
    /*
    Msg 213, Level 16, State 7, Procedure sp_spaceused, Line 128
    Column name or number of supplied values does not match table definition.
    */



    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    • Edited by Kalman Toth Thursday, September 27, 2012 5:03 PM
    Tuesday, January 5, 2010 6:29 PM
  • In the SQL Server Denali we can utilize multiple result sets. I did a very quick google search and I didn't find BOL links explaining exact syntax (didn't search hard enough), but as usual I found a good blog by Pinal Dave on this topic

    http://blog.sqlauthority.com/2011/04/29/sql-server-denali-executing-stored-procedure-with-result-sets-2/


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Marked as answer by Kalman Toth Friday, May 6, 2011 8:56 AM
    Sunday, May 1, 2011 10:10 PM
  • Abdshall, (or SQLUSA)

    Would it be possible to show the solution here?  That link is not on my approved list.  When I went home, I checked online and the blog does not exist.  So, what is the answer?  Or, can we push Naomi's answer up to the top? (That would be the "WITH RESULT SETS" solution from Pinal Dave).
    Thanks.


    R, J

    • Edited by Crakdkorn Saturday, September 29, 2012 7:17 PM
    Thursday, September 27, 2012 3:00 PM