SA0004 : Variable assigned but value never used |
![]() |
The topic describes the SA0004 analysis rule.

Variable assigned but value never used

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

review the variable usages and remove the unused variable assignment.

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

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 |

The rule does not need Analysis Context or SQL Connection.


Performance Rules, Code Smells

There is no additional info for this rule.

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) |

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 |
