SA0003 : Variable used but not previously assigned

Introduction

Usage of uninitialized variables can lead to potential errors or unpredictable behavior.

Description

Using variables in T-SQL without first assigning them a value can lead to unexpected behaviors in SQL Server. This is because uninitialized variables may lead to undefined values being used in calculations, conditions, or data manipulations, which can result in incorrect query results or application logic errors.

Example of problematic query:

DECLARE @TotalPrice DECIMAL(10, 2);
SELECT @TotalPrice = @TotalPrice + Price FROM Orders WHERE OrderID = 1;

In this example, @TotalPrice is used in a calculation before it is initialized. Since it does not have a predefined value, the result of the calculation could be incorrect or vary unexpectedly, leading to potential errors in the query results.

Potential for null or undefined behavior if the variable is used without initialization.
May lead to erroneous data in reporting or application logic, impacting business decisions.
SQL Server might not always raise an error, making it difficult to trace the problem.

How to fix

Ensure that all variables in T-SQL code are properly initialized before they are used in expressions or computations to prevent unexpected behavior and incorrect results.

Follow these steps to address the issue of using uninitialized variables:

1.Identify all variables in your T-SQL code that are used without prior initialization. Check for their usage in calculations, conditionals, or data manipulation statements.

2.Initialize each variable with an appropriate default value immediately after declaring it using the SET or SELECT statements. Ensure that the initial value logically aligns with the intended use.

3.Review and update any logic depending on these variables to ensure they behave as expected now that they are initialized correctly.

Corrected query with initialized variable:

DECLARE @TotalPrice DECIMAL(10, 2);
SET @TotalPrice = 0;
SELECT @TotalPrice = @TotalPrice + Price FROM Orders WHERE OrderID = 1;

Scope

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

Parameters

Name	Description	Default Value
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

8 minutes per issue.

Additional Information

There is no additional info for this rule.

Example Test SQL

DECLARE  @StartDate AS INT

SELECT  *
FROM  Orders
WHERE  StartDate >= @StartDate

Analysis Results

	Message	Line	Column
1	SA0003 : Variable @StartDate used but not previously assigned.	5	20

Analysis Rules