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

Rule has no parameters.

Remarks

The rule does not need Analysis Context or SQL Connection.

Categories

Design Rules, Bugs

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

  Message Line Column
1 SA0121 : Output parameter @Param3 is not populated in code path ending at line 48. 8 0
2 SA0121 : Output parameter @Param4 is not populated in code path ending at line 21. 9 0
3 SA0121 : Output parameter @Param4 is not populated in code path ending at line 48. 9 0
See Also

Other Resources