SA0192 : Procedure returns more than one result set

In SQL Server, a common issue arises when stored procedures are designed to return multiple result sets. This can lead to unexpected behavior in applications consuming these procedures and can complicate the integration with client-side applications expecting a single result set.

For example:

1-- Example of a stored procedure returning multiple result sets
2CREATE PROCEDURE RetrieveData
3AS
4BEGIN
5    SELECT * FROM Customers;
6    SELECT * FROM Orders;
7END;

This example demonstrates how executing the stored procedure RetrieveData will send multiple result sets to the client, which can confuse consuming applications that are not designed to handle more than one result.

• Confuses client applications expecting a single result set, potentially causing errors or unintended behavior.

• Increases the complexity of database and application maintenance due to handling of multiple sets of data.

This section guides you through ensuring that a stored procedure returns only a single result set to avoid potential issues with client applications.

Follow these steps to address the issue:

1. Review the stored procedure to identify any SELECT statements that may contribute to multiple result sets.

2. Determine if it is necessary to return all result sets, or if logic can be combined or consolidated into a single result set. Use UNION or JOIN operations to merge data into one set if possible.

3. Modify the stored procedure to ensure only one result set is returned. Alter the SELECT queries as required.

For example:

 1-- Example of corrected stored procedure with a single result set
 2CREATE PROCEDURE RetrieveSingleData
 3AS
 4BEGIN
 5    SELECT 
 6        Customers.CustomerID, 
 7        Customers.CustomerName, 
 8        Orders.OrderID, 
 9        Orders.OrderDate
10    FROM Customers
11    JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
12END;

The rule has a Batch scope and is applied only on the SQL script.

Rule has no parameters.

The rule does not need Analysis Context or SQL Connection.

Design Rules, Bugs

Return Data from a Stored Procedure

Other Resources