SA0115 : Ensure variable assignment from SELECT with no rows

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:

SQL
Copy
1-- Example of a query that might not update variables as expected
2DECLARE @Variable INT;
3SET @Variable = 1;
4SELECT @Variable = ColumnName FROM TableName WHERE SomeCondition = 'value';
5-- 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:

SQL
Copy
1-- Example of corrected query
2DECLARE @Variable INT;
3SET @Variable = NULL; -- Initialize the variable
4IF EXISTS(SELECT 1 FROM TableName WHERE SomeCondition = 'value')
5BEGIN
6    SELECT @Variable = ColumnName FROM TableName WHERE SomeCondition = 'value';
7END

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.
Categories

Design Rules, Bugs

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
Copy
 1CREATE PROCEDURE [dbo].[proc_SqlEnlight_Test_SA0115]
 2  @input int,
 3  @input2 int = 5,
 4  @output int output
 5AS
 6
 7SELECT @output = UpdatedWho, @input2 = 6 FROM dbo._asBlockHist WHERE UpdatedWho = @input;
 8SELECT @output
 9
10-- Case 1: Always referenced using ISNULL
11DECLARE @Supplier CHAR(5)
12SELECT @Supplier = Supplier FROM MDID_TRAN.dbo.TrOrderPO
13WHERE PoNum = '10101234'
14SELECT ISNULL(@Supplier, 'XXXXX')
15
16-- Case 2: Not ensured assigned but not used
17DECLARE @NotEnsuredAssignedSupplier1 CHAR(5)
18--SET @NotEnsuredAssignedSupplier1 = 'XXXXXX'
19SELECT @NotEnsuredAssignedSupplier1 = Supplier FROM MDID_TRAN.dbo.TrOrderPo WHERE PoNum = '10101234'
20--SELECT @NotEnsuredAssignedSupplier1
21
22-- Case 3: Default value assigned
23DECLARE @Supplier2 CHAR(5)
24SELECT @Supplier2 = 'XXXXXX'
25SELECT @Supplier2 = Supplier FROM MDID_TRAN.dbo.TrOrderPo WHERE PoNum = '10101234'
26SELECT @Supplier2
27
28-- Case 4: Not handled - should generate SA0115 rule violation
29DECLARE @NotEnsuredAssigned CHAR(5)
30-- SET @NotEnsuredAssigned = 'XXXXXX'
31SELECT @NotEnsuredAssigned = Supplier FROM MDID_TRAN.dbo.TrOrderPo WHERE PoNum = '10101234'
32SELECT @NotEnsuredAssigned
33
34-- Case 5: Not handled but will be ignored
35DECLARE @NotEnsuredAssigned1 CHAR(5)
36SELECT @NotEnsuredAssigned1 = Supplier /*IGNORE:SA0115*/ FROM MDID_TRAN.dbo.TrOrderPo WHERE PoNum = '10101234'
37SELECT @NotEnsuredAssigned1
38
39-- Case 6:Default value assigned
40DECLARE @EnsuredAssigned2 CHAR(10) = 'DEFAULT'
41SELECT @EnsuredAssigned2 = Supplier FROM MDID_TRAN.dbo.TrOrderPo WHERE PoNum = '10101234'
42SELECT @EnsuredAssigned2
43
44-- Case 7: ROWCOUNT checked and default value assigned
45DECLARE @EnsuredAssigned3 CHAR(10)
46SELECT @EnsuredAssigned3 = Supplier FROM MDID_TRAN.dbo.TrOrderPo WHERE PoNum = '10101234'
47
48IF @@ROWCOUNT = 0
49BEGIN
50     -- Variable is assigned after @@ROWCOUNT check
51    SELECT @EnsuredAssigned3 = 'DEFAULT'
52END
53SELECT @EnsuredAssigned3
54
55-- Case 8: Variable is checked for NULL and assigned
56DECLARE @Supplier4 CHAR(5);
57SELECT @Supplier4 = Supplier 
58FROM MDID_TRAN.dbo.TrOrderPO
59WHERE PoNum = '10101234';
60
61IF ((@Supplier4 IS NULL))
62BEGIN
63        SET @Supplier4 = '';
64END;
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
See Also

Other Resources

Analysis Rules