SA0192 : Procedure returns more than one result set

The topic describes the SA0192 analysis rule.

Message

Procedure returns more than one result set

Description

The rule checks for CREAT/ALTER PROCEDURE statements and reports if the procedure returns more than one result set.

If multiple SELECT statements are run during the execution of the stored procedure, multiple result sets will be sent to the client.

How to fix

Review the stored procedure and if this behavior is intentional, the rule can be suppressed.

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.
Categories

Design Rules, Bugs

Additional Information
Example Test SQL
SQL
Copy
 1CREATE PROCEDURE test.spTest_SA0192
 2 @Param1 INT
 3AS 
 4SET  NOCOUNT ON;
 5
 6DECLARE  @Var1 INT
 7
 8IF(@Param1 = 1) SELECT  1
 9ELSE
10BEGIN
11    WITH  c( col1, col2) AS (SELECT  1, 2)
12    SELECT  2
13SELECT  2
14END
15
16SELECT  3
17
18SELECT  @Var1 = 2
Analysis Results
  Message Line Column
1 SA0192 : Procedure returns more than one result set. 1 0
See Also

Other Resources

Analysis Rules