SA0121 : Output parameter is not populated in all code paths

This problem occurs when output parameters in stored procedures or functions are not properly set, leading to inconsistencies in results and potential application errors.

Description

Output parameters in stored procedures or functions are intended to be populated with data that may be needed by the caller. If these parameters are left unset in any execution path, it can result in unreliable behavior, since the caller might assume that the output parameter always carries a valid value.

For example:

SQL
Copy
 1CREATE PROCEDURE ExampleProcedure
 2    @inputParam INT,
 3    @outputParam INT OUTPUT
 4AS
 5BEGIN
 6    IF @inputParam > 0
 7    BEGIN
 8        -- Properly setting the output parameter
 9        SET @outputParam = 1;
10    END
11    -- Failing to set @outputParam when @inputParam is not greater than 0
12END

In this example, if @inputParam is not greater than 0, @outputParam remains unset. This can cause unexpected results or errors in the calling application, which may anticipate a defined value for continuation of its process.

  • Unreliable application behavior due to assumptions that all execution paths define the output value.

  • Potential runtime errors if consumer logic expects non-null or specific-value output.

How to fix

Ensure that output parameters in stored procedures or functions are always properly set to prevent inconsistencies and potential application errors.

Follow these steps to address the issue:

  1. Review the T-SQL code in stored procedures or functions to identify all execution paths leading to the termination point.

  2. Ensure that each execution path properly sets the output parameter by using the SET statement for the output parameter.

  3. Initialize the output parameter to a default value at the beginning of the procedure or function to ensure it is always set, even if no execution path explicitly assigns a new value.

For example:

SQL
Copy
 1CREATE PROCEDURE ExampleProcedure
 2    @inputParam INT,
 3    @outputParam INT OUTPUT
 4AS
 5BEGIN
 6    -- Initialize the output parameter
 7    SET @outputParam = 0;
 8
 9    IF @inputParam > 0
10    BEGIN
11        -- Properly setting the output parameter
12        SET @outputParam = 1;
13    END
14END

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
8 minutes per issue.
Categories

Design Rules, Bugs

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
Copy
 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 @Param4 is not populated in code path ending at line 21. 9 0
2 SA0121 : Output parameter @Param4 is not populated in code path ending at line 48. 9 0
See Also

Other Resources

Analysis Rules