SA0115 : Ensure variable assignment from SELECT with no rows

Introduction

A SELECT statement does not assign values to variables if no rows are returned, potentially leading to unexpected behavior and incorrect assumptions.

Description

This issue arises in T-SQL when developers expect a SELECT statement to always update variables, regardless of whether any rows are returned by the query. In SQL Server, if a query doesn’t find any matching rows, the variables remain unchanged rather than being reset or set to NULL . This can lead to logical errors and unexpected behavior in code execution.

For example:

-- Example of a query that might not update variables as expected
DECLARE @Variable INT;
SET @Variable = 1;
SELECT @Variable = ColumnName FROM TableName WHERE SomeCondition = 'value';
-- If no rows meet the condition, @Variable remains 1

In this example, if the SELECT statement returns no rows, @Variable will not be set to NULL or any new value. Instead, it retains its previous value, leading to potential inconsistencies in subsequent logic that relies on @Variable .

This behavior can cause data integrity issues if not properly safeguarded, as assumptions about variable values may be incorrect.
Unexpected results in business logic or data processing could occur if the condition for the SELECT is frequently unmet.

How to fix

Properly handle cases where a SELECT statement might not assign new values to variables.

Follow these steps to address the issue:

1.Initialize variables to a default or null state before the SELECT statement to avoid retaining previous values.

2.Use a conditional check to set the variable only if the SELECT statement returns rows. This can be done using IF EXISTS or another control flow statement.

3.Consider using a SELECT INTO statement to handle cases where no rows are found, isolating the logic and ensuring predictable outcomes.

For example:

    -- Example of corrected query
    DECLARE @Variable INT;
    SET @Variable = NULL; -- Initialize the variable
    IF EXISTS(SELECT 1 FROM TableName WHERE SomeCondition = 'value')
    BEGIN
        SELECT @Variable = ColumnName FROM TableName WHERE SomeCondition = 'value';
    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

13 minutes per issue.

Additional Information

There is no additional info for this rule.

Example Test SQL

CREATE PROCEDURE [dbo].[proc_SqlEnlight_Test_SA0115]
  @input int,
  @input2 int = 5,
  @output int output
AS

SELECT @output = UpdatedWho, @input2 = 6 FROM dbo._asBlockHist WHERE UpdatedWho = @input;
SELECT @output

-- Case 1: Always referenced using ISNULL
DECLARE @Supplier CHAR(5)
SELECT @Supplier = Supplier FROM MDID_TRAN.dbo.TrOrderPO
WHERE PoNum = '10101234'
SELECT ISNULL(@Supplier, 'XXXXX')

-- Case 2: Not ensured assigned but not used
DECLARE @NotEnsuredAssignedSupplier1 CHAR(5)
--SET @NotEnsuredAssignedSupplier1 = 'XXXXXX'
SELECT @NotEnsuredAssignedSupplier1 = Supplier FROM MDID_TRAN.dbo.TrOrderPo WHERE PoNum = '10101234'
--SELECT @NotEnsuredAssignedSupplier1

-- Case 3: Default value assigned
DECLARE @Supplier2 CHAR(5)
SELECT @Supplier2 = 'XXXXXX'
SELECT @Supplier2 = Supplier FROM MDID_TRAN.dbo.TrOrderPo WHERE PoNum = '10101234'
SELECT @Supplier2

-- Case 4: Not handled - should generate SA0115 rule violation
DECLARE @NotEnsuredAssigned CHAR(5)
-- SET @NotEnsuredAssigned = 'XXXXXX'
SELECT @NotEnsuredAssigned = Supplier FROM MDID_TRAN.dbo.TrOrderPo WHERE PoNum = '10101234'
SELECT @NotEnsuredAssigned

-- Case 5: Not handled but will be ignored
DECLARE @NotEnsuredAssigned1 CHAR(5)
SELECT @NotEnsuredAssigned1 = Supplier /*IGNORE:SA0115*/ FROM MDID_TRAN.dbo.TrOrderPo WHERE PoNum = '10101234'
SELECT @NotEnsuredAssigned1

-- Case 6:Default value assigned
DECLARE @EnsuredAssigned2 CHAR(10) = 'DEFAULT'
SELECT @EnsuredAssigned2 = Supplier FROM MDID_TRAN.dbo.TrOrderPo WHERE PoNum = '10101234'
SELECT @EnsuredAssigned2

-- Case 7: ROWCOUNT checked and default value assigned
DECLARE @EnsuredAssigned3 CHAR(10)
SELECT @EnsuredAssigned3 = Supplier FROM MDID_TRAN.dbo.TrOrderPo WHERE PoNum = '10101234'

IF @@ROWCOUNT = 0
BEGIN
     -- Variable is assigned after @@ROWCOUNT check
    SELECT @EnsuredAssigned3 = 'DEFAULT'
END
SELECT @EnsuredAssigned3

-- Case 8: Variable is checked for NULL and assigned
DECLARE @Supplier4 CHAR(5);
SELECT @Supplier4 = Supplier
FROM MDID_TRAN.dbo.TrOrderPO
WHERE PoNum = '10101234';

IF ((@Supplier4 IS NULL))
BEGIN
  SET @Supplier4 = '';
END;

Analysis Results

	Message	Line	Column
1	SA0115 : Variable @output assignment from SELECT with no rows not ensured.	7	7
2	SA0115 : Variable @NotEnsuredAssigned assignment from SELECT with no rows not ensured.	31	7

Analysis Rules