QuickQuestion series is a series of short posts in which I answer database related questions asked by my colleagues, friends, and co-workers, mainly application developers.
Is it OK to create a Stored Procedure that returns two result sets?
As always, in case of such questions, the only correct answer is: It depends! You need to consider several factors to make a decision. The most important thing is to realize what you really want to achieve and what are your constraints.
A Stored Procedure with multiple result sets
First of all, I would like to show that is possible to have a Stored Procedure that returns multiple result sets. To create it you just need to put at least two select statements in the Stored Procedure definition.
CREATE PROCEDURE dbo.usp_WithTwoResultSets AS SELECT 'First result set.' AS Result; SELECT 'Second result set.' AS Result; GO EXEC dbo.usp_WithTwoResultSets;
You can see the result:
There is even built-in system Stored Procedure called
sp_spaceusedthat also returns two result sets.
As you can see, even Microsoft developers used such approach for some of SQL Server functionalities. Does it already mean that is correct and desired approach? Not necessarily…
Like with everything, every solution has pros and cons. To describe them I’m going to compare one Stored Procedure returning two result sets with two Stored Procedures returning one result set each.
- Code deduplication
One Stored Procedure helps you to decrease the amount of duplicated SQL code. You don’t have to put two exactly same (or very similar) queries in two separate Stored Procedures. It also can help with further refactoring and prevent situations when only one SP is changed because the second one was forgotten.
- Supported by most of current DB APIs
Multiple result sets are supported by all major DB APIs, like ADO.NET, EntityFramework, ODBC, OLDB, JDBC, etc… It shouldn’t be an issue to process in application multiple result sets returned by Stored Procedure execution.
- Network usage
With one Stored Procedure, you can reduce the network usage. An application will send only one request and receive one response. With two Stored Procedures application has to send two requests and will receive two responses. In most cases that will generate more network traffic.
In a case when data for both results sets come from the same tables, the approach with one Stored Procedure can help you reduce the amount of data page reads and locks acquired on used tables. It might be helpful in very busy systems with a huge volume of queries running concurrently. Of course, it depends on queries and it does not always have to be that way.
- Code reusability
A combination of two queries within one Stored Procedure makes code less reusable. You need to execute all queries even if you’re interested only in getting one result set. That can significantly decrease overall code performance.
There is no possibility to process multiple returned result sets in T-SQL. That means it will be useful only when called from an application. In T-SQL to reuse code in subqueries, you will have to create another Stored Procedures or functions. Unfortunately, that will increase the amount of duplicated code in your codebase.
- Complicated code
Approach to create one Stored Procedure with multiple result sets fosters the formation of multi-purpose objects. In general, this is a very bad idea and leads to having a low-quality code base, that is extremely hard to be refactored. To keep code simple, predictable, and testable you should always try to follow single responsibility principle.
As stated before. In a case when you need only one of two result sets you still need to execute code responsible for the other part. That generates unnecessary reads and locks and generates additional load.
Another case is cached execution plans. It’s very probable that queries used in stored procedure depend on its parameters. This creates the possibility for occurrences of the parameter sniffing issue. It can be easily solved by splitting queries into two independent Stored Procedures.
What to do?
You need to ask yourself a question “What is my goal?“. That is the first and most important step to make a proper decision. The second question you should ask yourself is “What are my constraints?“.
If you care about code simplicity, clarity, and reusability then you shouldn’t create Stored Procedures with multiple result sets. If you are concerned about code performance, concurrency, and amount of generated reads and locks, then you may consider this approach, but in most cases, you should be able to achieve desired performance using other techniques.
The only case that would force me to consider the creation of one Stored Procedure with multiple result sets is a problem with excessive network usage. However, even in such situation, I would firstly look for other solutions because the number of drawbacks and further possible implications are overwhelming.
My general advice is to try to find another solution, before you make a decision to implement one Stored Procedure with multiple result sets. I cannot say it’s always bad, but in my opinion, it is bad in 99% of cases.