Solved

How do I identify multiple result sets returned by a single stored proc?

Posted on 2004-04-14
18
397 Views
Last Modified: 2012-05-04
I'm a developer working with stored procedures written by a DBA (on SQL server). One of the stored procedures returns multiple result sets. Each result set represents data from slightly different tables, the column names are the same, but the data needs to load into different spots.

I'd like some way for me to access these result sets by name rather than just depending on the ordering of the results that come back. Is there a simple way to "name" the result sets? I suggested adding an extra column to each result set - but that was frowned upon. I'm worried that the stored proc might get altered in the future and the ordering of the result sets will change (thus wreaking havoc throughout my data access code).

0
Comment
Question by:yore
  • 5
  • 5
  • 4
  • +2
18 Comments
 
LVL 6

Accepted Solution

by:
billy21 earned 200 total points
ID: 10825100
The order in which they are executed in the SP is the order they are returned in.  ie. if loaded into a dataset object you will find the first recordset is table(0) and the second table(1) and so on.

The only way I can think of is to create more result sets.

ie.

Select 'Clients result set'

select * from clients

Select 'Supliers result set'

select * from suppliers


Doing this you get extra resultsets that represent headers that explain what the next result set is going to be.  does that help?

0
 
LVL 34

Expert Comment

by:arbert
ID: 10825242
Why even add the extra selects--just add an extra column on the results:

Select 'Clients result set' as FromData, * from clients
SELECT  'Supliers result set' as FromData,* from Suppliers

The first column will contain where the data came from....
0
 
LVL 6

Expert Comment

by:billy21
ID: 10825286
Yep that would work.  I was just thinking in terms of the amount of redundant data that would then be parsed back when that column repeats itself for each record in the resultset.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 10825345
If you are using ADO, than you can use the NextRecordset to get subsequent recordsets.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 10825374
In other words (using VB):


Set rs = cmd.Execute        ' This gives you the first resultset
' Add code here to process the information from the first resultset

Set rs = rs.NextRecordset  ' This gives you the second resultset
' Add code here to process the information from the second resultset

and so on.
0
 
LVL 6

Expert Comment

by:billy21
ID: 10825399
acp.  I think yore knows how to get the next recordset.  s/he is worried that the order will change and is looking for a way to identify the recordset by means other than using numbers.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 10825471
Ah, I see.  I overlooked that.
0
 
LVL 34

Expert Comment

by:arbert
ID: 10825485
"I was just thinking in terms of the amount of redundant data that would then be parsed back when that column repeats itself for each record in the resultset."

Agree--only an example.  You could always make an int field with the "recordset number" to reduce the amount of data....
0
 
LVL 1

Author Comment

by:yore
ID: 10826180
arbert, yours is the same solution that the DBAs don't like.

billy, good idea. I'll see if that works for the DBA.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 13

Expert Comment

by:danblake
ID: 10826212
...

Also from the post:
Is there a simple way to "name" the result sets? I suggested adding an extra column to each result set - but that was frowned upon.
(No)

So adding extra columns/items will not be possible (or is not liked..)....
The person that 'frowned' upon the extra column (such as an int as suggested by arbert/other's) does he have any suggestions ?

Quite often somebody may frown upon a mechanism but it may be the only (sensible) way of generating the results as required.
(I should imagine because this would require hard-coding of an int at each possible stage..., unless you implement something like:
declare @section_number
set @section_number = 1

select <results>,@section_number as section from <table_name>
set @Section_number = @section_number + 1...
....
...
)


There is no way (YET) to identify a record-set from within a SP and give it a particular name (in-built component) that will only be picked up from the output as required.  Unfortunatly this is a bane for developers and results in all-sorts of problems when a new section is added within a SP and dependencies are not properly tracked between application sub-components.
Prehaps you would like to suggest this to MS: welisten@microsoft.com

Unless you program some clever logic to derive the SP code required from an external data-table/section no/named section to ensure that ordering/data results do not matter (But this is messy, may results in lots of recompiles/dynamic sql -- depending on the system/parameters used -- may be what you want ...) and makes the whole process fully data-driven independant of sectioning within an SP and can also be used to track if a SP actually completes or not and individual section return codes/errors as rqd.

Otherwise I would think about splitting the SP into multiple SPs where possible so 1-SP = 1-Result Set.
(But this may be a bigger overhead/ not possible in your enviroment).

0
 
LVL 34

Expert Comment

by:arbert
ID: 10826377
"arbert, yours is the same solution that the DBAs don't like"  

But they allow you to return multiple result sets?  I would be interested to know their reasoning....
0
 
LVL 1

Author Comment

by:yore
ID: 10835738
They don't like billy's solution either...

This seems like the only clean way to return multiple result sets.

But they are saying it is "industry standard" to just return the results in a specific order. I don't get why they are being so stubborn! They say doing this increases the complexity of the stored proc.
0
 
LVL 34

Expert Comment

by:arbert
ID: 10835766
Personally, I wouldn't say there is anything "industry standard" about returning multiple resultsets in the first place.

I don't see how adding a column to each select statement increases complexity.  As long as you put comments at the top of the proc that explains why they're there....
0
 
LVL 1

Author Comment

by:yore
ID: 10835993
These result sets only have 3-4 columns to begin with. An extra column is a significant amount of redundant information. So I sort of see their point.

Anyway, I think billy's idea doesn't increase the size of returned data by much at all.
0
 
LVL 34

Expert Comment

by:arbert
ID: 10836788
"An extra column is a significant amount of redundant information"  

That's different than

"increases the complexity of the stored proc"....
0
 
LVL 6

Expert Comment

by:billy21
ID: 10840066
It is definitely not an industry standard to have stored procedures return multiple resultsets.  That's just bad design.

The only time I have ever found a need to have a stored procedure return multiple resultsets is to populate my grids.  I have a resultset for data, another for columns and a third rs for any special columns (to be represented as check boxes or such things).

The advantage of doing this is that the grids can be manipulated at a client site without having to update the client.  The reason we get away with it is that we have a standard in the office for a set order.  As each rs represents a specific type of data that our VB .Net procedure expects to receive they must be in a specific order.

Ultimately we intend to put this information into tables but even then we will still have 2-3 resultsets being returned.
0
 
LVL 6

Expert Comment

by:billy21
ID: 10840087
>The advantage of doing this is that the grids can be manipulated at a client site without having to update the client.

By that I mean I can manipulate the grids without having to update the application.

My stored procs look something like this...

Select col1, col2, col3, col4, col5, col6
From Table

Select 'Col1', '50', 'Col3', '60', 'col6', '70'

Select 'Col1', 'Red;Checked'

Rs 0 is always data
Rs 1 is always column data (which columns should be visible and their widths in pixels)
Rs 2 is always special representation of data (if not as text how should they be represented and what colour if applicable)
0
 
LVL 1

Author Comment

by:yore
ID: 10843199
Arbert,

Don't argue with me. I am on your side!

They are being dicks about it and won't even make the simple change. They are saying any change is too complex.  Next time I'm just going to require each result set be returned by a seperate stored proc.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now