Stored Procedure Returns Multiple Result Sets

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Stored Procedure Returns Multiple Result Sets

Author

Topic

mttate74
Starting Member

2 Posts

Posted - 2006-01-09 : 11:03:56

I support a 3rd party application with SQL Server backend and ASP/.Net front end. I want to reuse a good bit of the SQL code for reporting purposes, but having problems with some of their stored procedures that return multiple result sets. In most cases, the first result set that is returned is a count of the number of records that will be returned. I would like to nest their stored procedure in my own custom stored procedure, but need to do some additional filtering. How can I 'ignore' the first result set returned and only go after the result set that I need to filter? I can not comment out lines in their own stored procedures for fear of wreaking havoc in the web part of the app. Here's a very crude example of how the stored procedures work (the real procedures contain several nested procedures and are by no means 5 lines long):

declare @rowcount int
set @rowcount = (select count(blah) from table where column like blahblah)
select @rowcount (returns recordcount)
select blah, blah from table where column like blahblah

The web app uses ADO.NextRecordset to navigate through the different result sets. Is there something similar that I can do inside of SQL where I store the first result set into a variable and then the second result set in a temp table to filter further?

Crudely, something like:

insert into @totalrecords, #temptable
exec storedprocedure

Ultimately, I want to pass one result set back to the web app for reporting and want to avoid doing the filtering on the web server side. I hope this makes sense and I'd appreciate any help someone can give. TIA

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-01-09 : 11:22:38

That is some truly crappy sql code. You sure you want to base your reports off of that?

If I were you, I would pirate the logic and the portions returning the results sets you need and create a separate set of stored procs following best practices design methodology (which does NOT include returning multiple result sets...).

mttate74
Starting Member

2 Posts

Posted - 2006-01-09 : 12:21:30

Well, I can't defend their choice of code in this, but it is a very large vendor and a $1M application. It's a new version and I know their support and custom development for the app goes to India/Pakistan now, but not sure if the primary development took place there, although I have my suspicions. Regardless, I could try to recreate a good chunk of their functionality as my own stored procs, but if there's a way to somehow utilize what is already written I wanted to explore that option first and foremost. I do not know of a way, but I'm not a guru by any means, so wanted to open it up to others who may be more advanced than I am. Is there a way, in SQL, to split up multiple returned result sets from a stored procedure (without doing some ActiveX DTS task)?

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-01-09 : 13:00:05

quote:
Originally posted by mttate74

Well, I can't defend their choice of code in this, but it is a very large vendor and a $1M application.

The correlation between vendor size or application cost to code quality is absolutely zero.
If you try to write a wrapper sproc that parses out only the datasets you need from the original sprocs, you will just end up with a bigger hack than you are already dealing with. If I came across such a situation at a new client I would either gag or giggle, depending upon whether my contract was fixed bid or time-and-materials.

druer
Constraint Violating Yak Guru

314 Posts

Posted - 2006-01-09 : 15:34:40

This vendor is certainly not the first, nor the last, to have stored procedures return multiple result sets that are assumed to be utilized only within "their" application. And sometimes we are all forced to work with "non perfect" conditions. They didn't write the stored procedure so it would work for a reporting situation for you, they wrote it to work with their application code. As such, I'm not aware of any way in pure Transact SQL code for you to say "I want the third result set from the query I'm passing to you in Query Analyzer. As far as I know you'll need to write your own application code in some language to pull out just what you want. You could write the code and compile it as a .DLL and then create an Extended Stored Procedure or something so that you could call it within Query Analyzer though. Making it generic like xp_FireAppProc @stored_proc_name = 'sp_blahblahblah', @result_set_nbr = 3.

SMerrill
Posting Yak Master

206 Posts

Posted - 2009-04-28 : 16:59:35

You are NOT alone!
Here are some further design comments:

My design is that SQL 2005 calls the .NET CLR, which reads the funky canned sproc and writes temp tables back into SQL. Then you can base views and sprocs off of that data.

Here are some pieces to make the CLR from:

Let's see: the CLR Sproc would have a custom table adapter used just for that sproc. Building the dataset would be easy enough ... Simply drag the sproc into the dataset designer and it will create the first DataTable for you. Name it Table01. Then run this...

BUILDING THE DATASET (RUN ONE TIME ONLY FOR EACH SPROC)
Imports System.Data.SqlClient
Imports System.Data

''' <summary>
''' Reads a Multiple result set Stored Procedure and makes an XSD file.
''' </summary>
''' <remarks></remarks>
Public Class SprocReader

Public Sub MakeXSD(ByVal ds As DataSet, ByVal cmdSelect As SqlCommand)
Dim dsRet As DataSet = CreateDatasetFromMultiResultSproc(ds, cmdSelect)
dsRet.DataSetName = ds.DataSetName
dsRet.WriteXmlSchema("Q:\MysterySprocSchema.xsd")
End Sub

Public Function CreateDatasetFromMultiResultSproc(ByVal ds As DataSet, ByVal cmdSelect As SqlCommand) As DataSet
Dim ret As DataSet = ds.Clone
cmdSelect.Connection.Open()
Dim reader As SqlDataReader = cmdSelect.ExecuteReader

Dim x As Integer = 1

reader.NextResult() 'throw out the first table; it is already there.
Do
x += 1
Dim strTablename As String = String.Format("Table{0:00}", x)
Dim dtSchema As System.Data.DataTable = reader.GetSchemaTable
ret.Tables.Add(MakeTableFromSchema(dtSchema, strTablename))
Loop Until Not reader.NextResult()
Return ret
End Function

Private Function MakeTableFromSchema(ByVal SchemaTable As DataTable, ByVal strTableName As String) As DataTable
Dim tblRet As DataTable
tblRet = New DataTable(strTableName)

For Each row As DataRow In SchemaTable.Rows
Dim colRet As DataColumn
' Create each new DataColumn and add to the DataTable.
colRet = New DataColumn()
With colRet
.DataType = row("DataType")
.ColumnName = row("ColumnName")
.ReadOnly = row("IsReadOnly")
.Unique = row("IsUnique")
If .DataType.Equals(System.Type.GetType("System.String")) Then
.MaxLength = row("ColumnSize")
End If
.AutoIncrement = row("IsAutoIncrement")
.AllowDBNull = row("AllowDBNull")

' Add the Column to the DataColumnCollection.
tblRet.Columns.Add(colRet)
End With
Next

Return tblRet
End Function

End Class

WRITING A CUSTOM TABLE ADAPTER
Now, you'll need to write a custom table adapter to fill each of the tables.

Dim cmdSelect As SqlCommand = New SqlCommand("dbo.mystery_sproc")
With cmdSelect

If .Connection.State = Data.ConnectionState.Closed Then
.Connection.Open()
End If
Dim reader As SqlDataReader = .ExecuteReader() 'RUN ALL NINE SELECT STATEMENTS
Dim iResult As Integer = 0
Do
iResult += 1
Dim strTablename As String = String.Format("Table{0:00}", iResult)
While reader.Read()
Dim row As Data.DataRow = Tables(strTablename).NewRow
For iField As Integer = 0 To reader.FieldCount - 1
row(iField) = reader(iField)
Next iField

Tables(strTablename).Rows.Add(row)
End While
Loop Until Not reader.NextResult 'ADVANCE TO NEXT SELECT STATEMENT
reader.Close()
End With

USAGE:
I'll leave it up to the next contestant to make it into a CLR procedure. But I think the SqlBulkCopy.WriteToServer object can write a DataReader back out to the server, so my theory is that SQL 2005 calls .NET CLR which reads the sproc and writes temp tables back into SQL. Then you can base views off of that data.

Please Let me know how it goes!

~ Shaun Merrill
Seattle, WA

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2009-05-01 : 10:39:18

Could you repeat that please? I lost consciousness at "CLR".

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources