• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 449
  • Last Modified:

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

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
yore
Asked:
yore
  • 5
  • 5
  • 4
  • +2
1 Solution
 
billy21Commented:
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
 
arbertCommented:
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
 
billy21Commented:
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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
Anthony PerkinsCommented:
If you are using ADO, than you can use the NextRecordset to get subsequent recordsets.
0
 
Anthony PerkinsCommented:
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
 
billy21Commented:
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
 
Anthony PerkinsCommented:
Ah, I see.  I overlooked that.
0
 
arbertCommented:
"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
 
yoreAuthor Commented:
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
 
danblakeCommented:
...

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
 
arbertCommented:
"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
 
yoreAuthor Commented:
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
 
arbertCommented:
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
 
yoreAuthor Commented:
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
 
arbertCommented:
"An extra column is a significant amount of redundant information"  

That's different than

"increases the complexity of the stored proc"....
0
 
billy21Commented:
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
 
billy21Commented:
>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
 
yoreAuthor Commented:
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 5
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now