Click or drag to resize

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.

Such procedures should be reviewed 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.

Categories

Design Rules, New Rules

Additional Information
Example Test Script
 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

 MessageLineColumn
1SA0192 : Procedure returns more than one result set.10
See Also

Other Resources