SA0004 : Variable assigned but value never used

The topic describes the SA0004 analysis rule.

Message

Variable assigned but value never used

Description

This rule checks for values assigned to a variable that are either not referenced afterwards or overwritten before being used.

How to fix

review the variable usages and remove the unused variable assignment.

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

Performance Rules, Code Smells

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
  1DECLARE @EmployeeID AS int
  2DECLARE @Birthdate AS datetime -- set default value
  3DECLARE @Title nvarchar(100) = 'Title'
  4
  5SET @Birthdate='1979-01-11 00:00:00.000'
  6
  7SELECT @Birthdate=Birthdate
  8FROM Employee
  9WHERE EmployeeID=21
 10
 11SELECT @EmployeeID=@EmployeeID -- Ignored as an assignment
 12
 13SELECT @EmployeeID=315
 14
 15EXEC mysp_test1 @EmployeeID OUT
 16
 17EXEC mysp_test2 @EmployeeID=@EmployeeID OUT
 18
 19IF(@Birthdate IS NOT NULL)
 20BEGIN
 21    SET @EmployeeID=12
 22END
 23-- The SELECT statement may not return any rows and is considered a conditional assignment and is not considered as to be overwriting the previous @EmployeeID value.
 24-- Even the statement is considered a conditional one, the eventual value that is set to the variable @EmployeeID is still checked for being overwritten or not used.
 25
 26SELECT TOP 1
 27 @EmployeeID=EmployeeID
 28FROM Employee
 29WHERE Birthdate=@Birthdate -- Assign new value to the  variable which overwrites all previous values. The line of this assignment will be displayed as a value overwritting statement for the previous varianble values.
 30
 31SET @EmployeeID=143
 32
 33SELECT *
 34FROM Employee
 35WHERE BirthDate>@Birthdate
 36      AND EmployeeID IN( 12,
 37                         22,
 38                         @EmployeeID)
 39
 40DECLARE @tmp AS TABLE( ID int);
 41DECLARE @a AS int;
 42
 43INSERT INTO @tmp( ID)
 44VALUES ( 1);
 45
 46SET @a=1;
 47
 48UPDATE @tmp SET @a=2
 49WHERE ID=2;
 50
 51PRINT @a
 52
 53DECLARE @HasTaskResponsibility AS char(1),
 54        @StartDateLookupLibId AS int
 55
 56SET @HasTaskResponsibility='Y'
 57
 58IF(@StartDateLookupLibId>0
 59   AND @StartDateLookupLibId<100)
 60BEGIN
 61    SET @HasTaskResponsibility='N'
 62END
 63
 64SET @HasTaskResponsibility='y'
 65
 66
 67
 68DECLARE @Language VARCHAR(5)
 69
 70SET @Language=(SELECT UPPER(Language)
 71                           FROM LocalizationContext)
 72
 73IF @Language IS NULL 
 74   OR @Language=''
 75SET @Language='EN-AU'
 76
 77DECLARE @Actions AS TABLE(
 78                        ActionType VARCHAR(10),
 79                        ExternalID UNIQUEIDENTIFIER,
 80                        LinkID BIGINT,                        
 81                        Enabled BIT, PRIMARY KEY(ActionType, ExternalID))
 82
 83INSERT INTO @Actions VALUES( 'Test1','{EDFA789E-A820-4D09-A50F-7F16904284B4}',0010000019,0)
 84UPDATE @Actions SET ActionType = 'Test3' WHERE ActionType = 'Test1'  AND ExternalID = '{EDFA789E-A820-4D09-A50F-7F16904284B4}'
 85
 86-- SELECT * FROM @Actions
 87
 88
 89
 90DECLARE @var1 int
 91
 92SET @var1 = 1 -- Value won't be used as it is overwritten by the assignment 'SET @var1 = 2'
 93
 94SET @var1 = @var1 -- The assignmet is ignored as it will not change variable value.
 95
 96SET @var1 = 2 -- This assignemt here will overwrite all previous variable @var1 values.
 97
 98SELECT @var1
 99
100DECLARE @dialog_handle UNIQUEIDENTIFIER,
101                @ExpenseReport XML ;
102
103SET @ExpenseReport = '< construct message as appropriate for the application >' ;
104
105BEGIN DIALOG @dialog_handle
106FROM SERVICE [//Adventure-Works.com/Expenses/ExpenseClient]
107TO SERVICE '//Adventure-Works.com/Expenses'
108ON CONTRACT [//Adventure-Works.com/Expenses/ExpenseProcessing] ;
109
110BEGIN CONVERSATION TIMER (@dialog_handle)
111TIMEOUT = 120 ;
112
113SEND ON CONVERSATION @dialog_handle
114        MESSAGE TYPE [//Adventure-Works.com/Expenses/SubmitExpense]
115        (@ExpenseReport) ;
116
117DECLARE @ErrorSave INT
118
119SET @ErrorSave = @@ERROR;
120
121IF (@ErrorSave <> 0)
122BEGIN
123
124        DECLARE @ErrorDesc NVARCHAR(100);
125
126        SET @ErrorDesc = N'An error has occurred.';
127
128        END CONVERSATION @dialog_handle 
129        WITH ERROR = @ErrorSave DESCRIPTION = @ErrorDesc;
130
131END
132ELSE
133BEGIN
134        RECEIVE *
135        FROM ExpenseQueue
136        WHERE conversation_handle = @dialog_handle ;
137END
138
139DECLARE  @LoopCount AS INT       
140DECLARE  @ModifiedInLoop INT
141DECLARE  @UsedOnlyInLoopBody INT
142SELECT   @ModifiedInLoop = 1, @UsedOnlyInLoopBody = 1
143WHILE(@LoopCount > 0)
144BEGIN
145
146        DECLARE @LoopLocal INT
147        SELECT    @LoopLocal = 1
148        SELECT    @LoopLocal = 3
149        SELECT    @LoopLocal = @LoopLocal + 2
150        SELECT    @LoopLocal
151
152    SELECT @LoopCount = @LoopCount - 1
153        SELECT @ModifiedInLoop = @ModifiedInLoop + 1
154
155        SELECT @UsedOnlyInLoopBody = @UsedOnlyInLoopBody + 1
156
157END
158SELECT    @ModifiedInLoop = @ModifiedInLoop + 1
159SELECT           @ModifiedInLoop
160
161DECLARE   @SuppressRuleTest AS INT       
162
163SELECT    @SuppressRuleTest = 1
164SELECT    @SuppressRuleTest = 2 -- IGNORE:SA0004(LINE)
165SELECT    @SuppressRuleTest = 3 
166SELECT    @SuppressRuleTest = 4
167SELECT    @SuppressRuleTest = 5
168SELECT    @SuppressRuleTest = 6 -- IGNORE:SA0004(STATEMENT)
169SELECT    @SuppressRuleTest = 7        
170SELECT    @SuppressRuleTest = 8 
171SELECT    @SuppressRuleTest = 9
172SELECT    @SuppressRuleTest = 10 -- IGNORE:SA0004
173SELECT    @SuppressRuleTest = 11 -- IGNORE:SA0004
174
175DECLARE @Delay DateTime
176DECLARE @Delay2 DateTime2 = '2013-01-01 00:00:00'
177DECLARE @Delay3 DateTime2 = '2013-01-01 00:00:00'
178
179WAITFOR DELAY @Delay
180WAITFOR DELAY @Delay2
181DECLARE @StringToExecute1 as nvarchar(100) = N'SELECT 1',
182        @StringToExecute2 as nvarchar(100) = N'SELECT 2'
183
184EXEC @StringToExecute1
185EXEC (@StringToExecute2)

Analysis Results
  Message Line Column
1 SA0004 : Variable @EmployeeID is re-assigned at line 31 without its previous value being used. 17 28
2 SA0004 : Variable @EmployeeID is re-assigned at line 31 without its previous value being used. 21 8
3 SA0004 : Variable @EmployeeID is re-assigned at line 31 without its previous value being used. 27 1
4 SA0004 : Variable @Title assigned but value never used. 3 8
5 SA0004 : Variable @HasTaskResponsibility is re-assigned at line 64 without its previous value being used. 56 4
6 SA0004 : Variable @HasTaskResponsibility is re-assigned at line 64 without its previous value being used. 61 8
7 SA0004 : Variable @HasTaskResponsibility assigned but value never used. 64 4
8 SA0004 : Variable @Language assigned but value never used. 75 4
9 SA0004 : Variable @var1 is re-assigned at line 96 without its previous value being used. 92 4
10 SA0004 : Variable @LoopLocal is re-assigned at line 148 without its previous value being used. 147 11
17 SA0004 : Variable @SuppressRuleTest assigned but value never used. 171 10
18 SA0004 : Variable @Delay3 assigned but value never used. 177 8
See Also

Other Resources