SA0002 : Variable declared but never referenced or assigned

Introduction

Declared but never used variables, result in unnecessary resource allocation and potential code clutter.

Description

Declaring variables without subsequent use or assignment can clutter the code and lead to confusion. This issue often arises during rapid development or refactoring, where variables may be declared in anticipation of use but ultimately remain unused. Unused variables can make the code harder to read and increase the cognitive load for developers trying to understand the flow and logic.

Example of problematic query with unused variable:

DECLARE @UnusedVariable INT;
SELECT * FROM Employees;

In this example, the variable @UnusedVariable is declared but never used in the SQL script. This is an issue because:

Unused variables can give the false impression that they play a role in the logic, misleading developers or reviewers.
They contribute to code bloat, which can make maintenance and debugging more challenging.

How to fix

Remove unused variable declarations from your SQL scripts to improve code quality and maintainability.

Follow these steps to address the issue:

1.Identify any variables that are declared but not used in your script. Look for statements like DECLARE @VariableName DataType; where the variable @VariableName is not subsequently referenced.

2.Remove the declaration of the unused variable to clean up the code. Ensure that any logic dependent on the variable is not affected (if originally anticipated for use).

3.Review your script to confirm that the removal of the unused variable does not impact the functionality of the remaining SQL statements. Look for any comments or planned sections that anticipated variable use and update them if necessary.

Example of corrected query without unused variable:

-- Removed 'DECLARE @UnusedVariable INT;'
SELECT * FROM Employees;

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters

Name	Description	Default Value
IgnoreDefaultValueAssignment	Parameter specifies whether the initialization of the variable to be considered assignment or not.	no

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix

2 minutes per issue.

Additional Information

There is no additional info for this rule.

Example Test SQL

DECLARE  @Title AS NVARCHAR( 50 )
DECLARE  @Title2 AS NVARCHAR( 50 ) = 'Title2'
DECLARE  @Birthdate AS DATETIME

SET @Birthdate = '1979-01-11 00:00:00.000'

SELECT  *
FROM  Employee
WHERE  BirthDate > @Birthdate

Analysis Results

	Message	Line	Column
1	SA0002 : Variable @Title declared but never used.	1	9
2	SA0002 : Variable @Title2 declared but never used.	2	9

Analysis Rules