SA0192 : Procedure returns more than one result set

Introduction

Ensuring that stored procedures return only a single result set is crucial for optimal performance and maintainability.

Description

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:

-- Example of a stored procedure returning multiple result sets
CREATE PROCEDURE RetrieveData
AS
BEGIN
    SELECT * FROM Customers;
    SELECT * FROM Orders;
END;

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.

How to fix

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:

-- Example of corrected stored procedure with a single result set
CREATE PROCEDURE RetrieveSingleData
AS
BEGIN
    SELECT
        Customers.CustomerID,
        Customers.CustomerName,
        Orders.OrderID,
        Orders.OrderDate
    FROM Customers
    JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
END;

Scope

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

Parameters

Rule has no parameters.

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix

3 hours per issue.

Additional Information

Return Data from a Stored Procedure

Example Test SQL

CREATE PROCEDURE test.spTest_SA0192
 @Param1 INT
AS
SET  NOCOUNT ON;

DECLARE  @Var1 INT

IF(@Param1 = 1) SELECT  1
ELSE
BEGIN
    WITH  c( col1, col2) AS (SELECT  1, 2)
    SELECT  2
SELECT  2
END

SELECT  3

SELECT  @Var1 = 2

Analysis Results

	Message	Line	Column
1	SA0192 : Procedure returns more than one result set.	1	0

Analysis Rules