SA0121 : Output parameter is not populated in all code paths

Introduction

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:

CREATE PROCEDURE ExampleProcedure
    @inputParam INT,
    @outputParam INT OUTPUT
AS
BEGIN
    IF @inputParam > 0
    BEGIN
        -- Properly setting the output parameter
        SET @outputParam = 1;
    END
    -- Failing to set @outputParam when @inputParam is not greater than 0
END

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:

CREATE PROCEDURE ExampleProcedure
    @inputParam INT,
    @outputParam INT OUTPUT
AS
BEGIN
    -- Initialize the output parameter
    SET @outputParam = 0;

    IF @inputParam > 0
    BEGIN
        -- Properly setting the output parameter
        SET @outputParam = 1;
    END
END

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.

Additional Information

There is no additional info for this rule.

Example Test SQL

CREATE PROCEDURE [dbo].test_SA0121
(
@Value1 BIGINT,
@Value2 INT,
@Value3 INT,
@Param1 INT OUTPUT ,
@Param2 INT OUTPUT ,
@Param3 INT OUTPUT ,/*IGNORE:SA0121*/
@Param4 INT OUTPUT
)
AS
BEGIN
-- Initialize the out parameter
SET @Param1 = 0;

IF (@Value1 IS NULL)
BEGIN
  EXEC @Param3 =  mysp_Test @Param2 OUT
  IF (@Value2 > 3)
  BEGIN
    RETURN 1;

        IF (@Param1 = 0)
        BEGIN
            SELECT @Param1 = 1
        END
        RETURN 2;
  END
  ELSE
  BEGIN
    IF (@Value3 = 'VALUE')
    BEGIN
      SELECT @Param1 = 1

            RAISERROR ('Raise error instead of using RETURN statement.', 16, 1)

      --RETURN 2;
    END
    ELSE
    BEGIN
      SET @Param2 = 2
      SET @Param4 = 3
      RETURN -1;
    END
  END
END

SET @Param2 = 5

END

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

Analysis Rules