SA0004 : Variable assigned but value never used

Introduction

Unused or prematurely overwritten variables can indicate redundant logic or result in wasted computational resources.

Description

Managing variables effectively is crucial for writing efficient and maintainable code. The problem addressed by this analysis is the assignment of values to variables that are never used or are overwritten before they can be utilized. This scenario leads to unnecessary operations and potential confusion about the code’s intent.

Example of a query with an unused variable:

DECLARE @UnusedVariable INT;
SET @UnusedVariable = 42;
-- @UnusedVariable is set but never used
SELECT * FROM Employees;

In this example, the variable @UnusedVariable is assigned a value but is never referenced afterwards, which may indicate a missed logic implementation or redundant code. Overwriting variables without utilizing their assigned values can similarly reduce code clarity and efficiency.

Unnecessary computations can increase the execution time needlessly.
Code readability and maintainability are reduced when irrelevant or redundant variables are present.

How to fix

This section provides guidance on addressing issues identified by the SQL Enlight analysis rule sa0004, which focuses on unused variable assignments in T-SQL code.

Follow these steps to address the issue:

1.Identify the variables that are declared but not used within your T-SQL code. Carefully review each variable assignment to determine if it’s genuinely redundant or part of a missed logic implementation.

2.Remove variable assignments for those that are not utilized anywhere in the subsequent code. Use -- for comments if you want to mark these unused lines temporarily before deletion.

3.Refactor your code logic if the variable was meant to serve a purpose but was overlooked or improperly placed. Ensure that each variable serves its intended role throughout the query.

4.Test your revised code to confirm that the logic is correct and executes efficiently without unnecessary variable assignments.

Corrected query without unused variables:

SELECT * FROM Employees;

Scope

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

Parameters

Name	Description	Default Value
IgnoreConditionalAssignments	Ignore assignments made inside IF-ELSE statements and filtered SELECT statements.	yes
IgnoreTableVariables	Ignore table variables which have values inserted, but not used in JOIN or FROM clause.	yes

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 @EmployeeID INT, @Birthdate DATETIME = '1979-01-11';

-- Overwritten without usage
SET @EmployeeID = 315;
SET @EmployeeID = 143;

-- Unused variable
DECLARE @UnusedVar INT = 1;

-- Conditional assignment overwritten
IF @Birthdate IS NOT NULL
    SET @EmployeeID = 12;

-- Loop with unused variable
DECLARE @LoopCount INT = 2, @ModifiedInLoop INT = 1;
WHILE @LoopCount > 0
BEGIN
    DECLARE @LoopLocal INT = 3;
    SET @LoopCount = @LoopCount - 1;
END;

SELECT @ModifiedInLoop;

Analysis Results

	Message	Line	Column
1	SA0004 : Variable @EmployeeID is re-assigned at line 5 without its previous value being used.	4	4
2	SA0004 : Variable @EmployeeID assigned but value never used.	5	4
3	SA0004 : Variable @EmployeeID assigned but value never used.	12	8
4	SA0004 : Variable @UnusedVar assigned but value never used.	8	8
5	SA0004 : Variable @LoopLocal assigned but value never used.	18	12

Analysis Rules