Solved

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

Posted on 2004-04-14
18
421 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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
 
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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.

830 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