Results 1 to 15 of 15
  1. #1
    Join Date
    Nov 2002
    Posts
    11

    Unanswered: Naming the "result sets" returned by stored procedure

    Greetings

    I have a SQL Server stored procedure that performs several queries and therefore returns several "result sets" at any one time.

    Wiring it up via ADO.NET I populate a DataSet with a number of items in the Tables collection - which is great - and I can give each item a name for identification purposes once the DataSet is populated.

    But I'd like to know if there is some way I can set the names of each result set *within the text of the stored procedure*, i.e. before the DataSet gets populated.

    Any help greatly appreciated.
    Stuart
    Aligned Assets Developers

  2. #2
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    Not sure what you are talking about here. You might want to give us an example of what you wanting to return and how it appears in the stored procedure. If you are talking about a simple description before each recordset, you can simply use:

    SELECT 'This is an example of a description for recordset 1'
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    A stored procedure that returns more than one results set is just not sound programming practice. You are asking for trouble if you try to build applications like this.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    Oh, go and mention that now. Gheesh!
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  5. #5
    Join Date
    Nov 2002
    Posts
    11
    Thanks for the responses!

    The stored procedure effectively contains a number of SELECT statements. When I run it through Query Analyzer I get a number of datagrids. And when I wire it up to an ADO.NET DataSet I get a collection of Table objects. I just need to know whether I can "name" each result set in the stored procedure so that when the data are propogated to the subsequent Table objects the name is propagaged too.

    Regarding whether or not this is good practise... please point me to a link explaining why!

    Thanks
    Stuart
    Aligned Assets Developers

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Here is a link that illustrates one of the problems you might encounter:

    http://www.dbforums.com/t1003582.html

    One function: one resultset.
    One procedure: one resultset.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by blindman
    Here is a link that illustrates one of the problems you might encounter:

    http://www.dbforums.com/t1003582.html

    One function: one resultset.
    One procedure: one resultset.
    Is this something like the definition of recursion?

    In order to understand recursion, first we must understand recursion.

    -PatP

  8. #8
    Join Date
    Nov 2002
    Posts
    11
    Quote Originally Posted by blindman
    Here is a link that illustrates one of the problems you might encounter:

    http://www.dbforums.com/t1003582.html

    One function: one resultset.
    One procedure: one resultset.
    Please explain.
    Aligned Assets Developers

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Most common application interfaces are only set up to deal with a single recordset being returned for each call. Crystal, for instance, would not be able to handle multiple recordsets. Unknown rowcounts, different records layouts, etc, make designing an application around such procedures very difficult.

    Also, bundling these recordsets together makes it impossible to return one without the others. That's extremely inefficient for the occasions when only one recordset is required.

    Beyond that, in my 10+ years of database application development I've never run into a situation where I needed to return multiple recordsets from a single database call. So I have to wonder what you are doing and why this is necessary. Could you please explain your application requirements?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Sep 2003
    Posts
    364
    I totally agree with blindman. We had a developer do this in Sybase. We had a helluva time with apps that made ODBC and JDBC connections to the db.

  11. #11
    Join Date
    Nov 2002
    Posts
    11
    Our database holds various information relating to land, streets and property. We have to provide the ability of exporting this information in a published format, with sets of different record types. The format of each record type differs, so the thinking was for a sproc to do all the work (convert from our format to the export format) and then utilise the power of an ADO.NET DataSet object to construct a Tables collection.

    This object may then be manipulated easily (as XML, via XSLT) for example.

    From an application point of view it seems more straightforward to call a sproc once to populate an object, rather than fifteen different calls to record type specific sprocs.

    I'll admit that the single stored procedure is rather hirsute, however :P
    Aligned Assets Developers

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The only way that I could justify this kind of design would be if communications cost was extorbitant, such as interplanetary distances. If that were the case, I'd still write the thing as a couple of dozen discrete procedures with a master "calling procedure" that would call each of the "worker" routines in turn (kind of like what sp_help does).

    The overall cost of maintaining the "hirstute" code would overwhelm any run-time savings in the long term.

    -PatP

  13. #13
    Join Date
    Nov 2002
    Posts
    11
    Well, I appreciate the advice. Thanks for your time.
    Aligned Assets Developers

  14. #14
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  15. #15
    Join Date
    Sep 2003
    Posts
    364
    Interesting article don't know if I'll ever use it but I can say I've learned something today.

Posting Permissions

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