Click or drag to resize

SA0121 : Output parameter is not populated in all code paths

The topic describes the SA0121 analysis rule.

Message

Output parameter is not populated in all code paths

Description

The rule checks stored procedures and functions, and reports if there are output parameters which are not populated in one, or more code paths.

This rule identifies and report in which path(s) the output parameter should be set.

Scope

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

Parameters
NameDescriptionDefault Value
RuleSuppressMark

Suppress rule reporting a rule violation in case a comment containing the value of this parameter is found close to the source of violation.

IGNORE:SA0121

Remarks

The rule does not need Analysis Context or SQL Connection.

Categories

Design Rules

Additional Information

There is no additional info for this rule.

Example Test Script
SQL
 1CREATE PROCEDURE [dbo].test_SA0121
 2(
 3@Value1 BIGINT,
 4@Value2 INT,
 5@Value3 INT,
 6@Param1 INT OUTPUT ,
 7@Param2 INT OUTPUT ,
 8@Param3 INT OUTPUT ,/*IGNORE:SA0121*/
 9@Param4 INT OUTPUT 
10) 
11AS 
12BEGIN
13-- Initialize the out parameter
14SET @Param1 = 0;
15
16IF (@Value1 IS NULL)
17BEGIN
18        EXEC @Param3 =  mysp_Test @Param2 OUT
19        IF (@Value2 > 3)
20        BEGIN
21                RETURN 1;
22
23        IF (@Param1 = 0) 
24        BEGIN
25            SELECT @Param1 = 1
26        END
27        RETURN 2;
28        END
29        ELSE
30        BEGIN
31                IF (@Value3 = 'VALUE')
32                BEGIN
33                        SELECT @Param1 = 1
34
35            RAISERROR ('Raise error instead of using RETURN statement.', 16, 1)
36
37                        --RETURN 2;
38                END
39                ELSE
40                BEGIN
41                        SET @Param2 = 2
42                        SET @Param4 = 3
43                        RETURN -1;
44                END
45        END
46END
47
48SET @Param2 = 5
49
50END

Analysis Results

 MessageLineColumn
1SA0121 : Output parameter @Param2 is not populated in code path ending at line 21.70
2SA0121 : Output parameter @Param3 is not populated in code path ending at line 21.80
3SA0121 : Output parameter @Param3 is not populated in code path ending at line 43.80
4SA0121 : Output parameter @Param3 is not populated in code path ending at line 50.80
5SA0121 : Output parameter @Param4 is not populated in code path ending at line 21.90
6SA0121 : Output parameter @Param4 is not populated in code path ending at line 50.90
See Also

Other Resources