March 17, 2009 at 11:08 pm
Is it a good practice to return multiple datasets in a stored procedure?
March 17, 2009 at 11:17 pm
Depends on your requirement...
If the resultsets have same columns then you can join them by UNION.
From performance point of view there's no big impact of returning multiple datasets.
March 18, 2009 at 6:11 am
Short answer, it depends.
Longer answer, you can reduce the number of round trips required to retrieve data and this can enhance performance. So instead of 3 seperate calls to the server to retreive three result sets, if you know you need three and you're always going to call for them at the same time, get all three from one call. It does help some. It's not a radical improvement, but it is an improvement. You need to make sure the client code can handle it though.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 19, 2009 at 1:29 pm
It depends.
It prevents multiple round trips to the database server, particularly if the multiple result sets are related. For example, if the logic requires a complex lookup to get the PKs, and possibly some other data, to be returned the stored procedure can populate a lookup table which can then be referenced by the subsequent queries within the stored procedure. Otherwise, the complex lookup logic would need to be executed "n" times.
Another case would be if the data returned by the separate calls needs to be consistent. For example, a Master/Detail application. With a single stored procedure call, you can encapsulate the locking and/or transaction isolation level within the single procedure. Otherwise you would need to ensure that the Business Logic Layer (BLL) makes the multiple calls with 1.) the correct transaction scope and 2.) makes the calls really tight together.
Multiple result sets works very well from a .Net standpoint as your DAL (Data Access Layer) can build a DataSet containing all of the tables returned by the stored procedure and return the DataSet object to the BLL.
March 19, 2009 at 7:13 pm
I somewhat regularly use multiple record sets for front end apps that are either .Net or some Java web front end. They are useful when you want to load let's say a list of orders and a list of order detail records in the front end. The user can select a specific order and then, without an additional call to the database, have the order details for the order displayed.
They are only useful when the record sets have different data in them and there's a definite relationship between the 2 sets.
Todd Fifield
July 24, 2017 at 9:09 am
I use stored procedures to return multiple record sets fairly often. Sometimes the data is formally related; sometimes not. It depends on the application. I use this only when the application needs multiple sets of data all at one time. For example in a web page that has multiple gird views, etc.
My latest usage was in an application that creates text output files for import into our financial system. Each of the imports references different tables in the financial system and uses different formats for the import. By pulling the tables all in one SP, I only make one call to SQL and the server returns my data in one trip. Then in my code I process the returned results as necessary.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply