October 3, 2007 at 4:23 pm
Almost all of the stored procedures I'm dealing with at my new gig return multiple result sets. I'm trying to execute a stored procedure and stuff one of the multiple result sets into a table. I'm forced to crack open the stored procedure in question, go through it line-by-line, and try to determine which chunk of the stored procedure created the particular result set that interests me.
For example:
INSERT INTO #some_table (some_column)
EXEC sp_return_some_column
The problem, for me, at least, occurs when the stored procedure returns multiple result sets. Sometimes upwards of 25 result sets are returned.
There's got to be a better way! Please help.
By the way, the multiple result sets have varying column names, datatypes, and number of columns.
Thanks in advance for your help.
October 3, 2007 at 4:34 pm
:w00t:!, the only that comes to mind quickly is rewrite the damn thing. HOwever I'm sure you've considered that option and ruled it out. The only other option I see is the copy the query you want to get and use that in the calling proc. One way to get around the code in multiple places would be to turn those statements into views or table functions. Other than that, I can't think of anything else at the moment.
October 3, 2007 at 5:25 pm
You cannot access resultsets past the first one from T-SQL code. It just cannot be done.
However, you could (and I haven't played much with the embedded .NET stuff but this ought to work), write a quick .NET assembly which you embed into your database that
1. Calls the proc in question using the provided connection handle
2. Obtains the desired resultset
3. Returns that resultset
A better way which would probably perform more efficiently is to (gulp) rewrite the stored procs.... You could do something as simple as tacking on another parameter indicating which resultset number you're after. It could default to -1 (negative 1) which indicates to return all resultsets. This has the benefit of
a) No other code that uses this proc needs to change as the default -1 value will be passed and behaviour will continue as expected
b) Your code can take advantage of the extra parameter to get exactly what you need.
You needn't use an int param, you could also take a varchar that defaults to NULL or blank (depending on your preference) to avoid changes in resultset ordering stuffing up your int param's meaning in the future. Good luck 😀
October 4, 2007 at 6:41 am
Stupid question, I know, but is it possible that the SET NOCOUNT ON is, you know, off inside these queries? Depending on how you make the call, I've seen rows affected appear as result sets.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
October 4, 2007 at 8:17 am
Thank you for taking the time to respond, I truly appreciate your suggestions. I'm resigned to the fact that I'll be re-writing a bunch of stored procedures.
October 4, 2007 at 8:42 am
It might make a difference what you are using to consume the output of the stored procedures. For example in Access vba, there is an ADO NextRecordset method that allows you to move to the next recordset in the response returned from SQL server.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply