Click or drag to resize

SA0003 : Variable used but not previously assigned

The topic describes the SA0003 analysis rule.

Message

Variable used but not previously assigned

Description

This rule checks for use of not initialized variables.

A variable is considered to be used, but no initialized if it has no value assigned to it before the statement where it is referenced.

Scope

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

Parameters
NameDescriptionDefault Value
IgnoreTableVariables

Ignore table variables which have values inserted, but not used in JOIN or FROM clause.

yes

RuleSuppressMark

Comment text which to indicate that the assignment operation should be ignored during rule evaluation.

IGNORE:SA0003

Remarks

The rule does not need Analysis Context or SQL Connection.

Categories

Design Rules

Additional Information

There is no additional info for this rule.

Example Test Script
  1DECLARE @HasTaskResponsibility AS char(1),
  2        @StartDateLookupLibId AS int
  3
  4SET @HasTaskResponsibility='Y'
  5
  6IF(@StartDateLookupLibId>0
  7   AND @StartDateLookupLibId<100)
  8BEGIN
  9    SET @HasTaskResponsibility='N'
 10END
 11
 12SET @HasTaskResponsibility='y'
 13
 14DECLARE @Title AS nvarchar(50)
 15DECLARE @Birthdate AS datetime --SET @Birthdate = '1979-01-11 00:00:00.000'
 16
 17IF(@Title IS NULL) SELECT @Title='Untitled'
 18
 19SELECT *
 20FROM Employee
 21WHERE BirthDate>@Birthdate
 22
 23SELECT @Title
 24
 25DECLARE @Incomplt AS int,
 26        @Buyernew AS int
 27
 28BEGIN
 29    SET nocount ON -- The cases when the @Incomplt variable is assigned to itself are ignored.
 30
 31    SELECT @Incomplt=CASE
 32                         WHEN TransactionStatusCode='INCOMPLT' THEN TransactionStatusLibId
 33                         WHEN TransactionStatusCode='INCOMPLT1' THEN @Incomplt -- Ignored as the variable is assigned to itself
 34
 35                         WHEN TransactionStatusCode='INCOMPLT1' THEN @Incomplt + 1
 36                         ELSE @Incomplt -- Ignored
 37
 38                     END
 39    FROM TransactionStatusLib -- Variable @Buyernew is assigned to itself and that's why it is ignored
 40
 41    SELECT @Buyernew=@Buyernew 
 42
 43    SELECT @Buyernew=@Buyernew + 1 -- This case is not ignored as the variable is included in expression
 44
 45------    
 46    DECLARE @data AS xml;
 47    DECLARE @eventType AS  sysname;
 48
 49    SET @data = EVENTDaTA(); -- Variable is assigned before it is used
 50
 51    SET @eventType=@data.value( '(/EVENT_INSTANCE/EventType)[1]',
 52                                'sysname');
 53
 54------
 55
 56
 57
 58        DECLARE @dialog_handle UNIQUEIDENTIFIER,
 59                        @ExpenseReport XML ;
 60
 61        SET @ExpenseReport = '< construct message as appropriate for the application >' ;
 62
 63        BEGIN DIALOG @dialog_handle
 64        FROM SERVICE [//Adventure-Works.com/Expenses/ExpenseClient]
 65        TO SERVICE '//Adventure-Works.com/Expenses'
 66        ON CONTRACT [//Adventure-Works.com/Expenses/ExpenseProcessing] ;
 67
 68
 69        BEGIN CONVERSATION TIMER (@dialog_handle)
 70        TIMEOUT = 120 ;
 71
 72        SEND ON CONVERSATION @dialog_handle
 73                MESSAGE TYPE [//Adventure-Works.com/Expenses/SubmitExpense]
 74                (@ExpenseReport) ;
 75
 76        DECLARE @ErrorSave INT
 77
 78        SET @ErrorSave = @@ERROR;
 79
 80        IF (@ErrorSave <> 0)
 81        BEGIN
 82
 83                DECLARE @ErrorDesc NVARCHAR(100);
 84
 85                SET @ErrorDesc = N'An error has occurred.';
 86
 87                END CONVERSATION @dialog_handle 
 88                WITH ERROR = @ErrorSave DESCRIPTION = @ErrorDesc;
 89
 90        END
 91        ELSE
 92        BEGIN
 93                RECEIVE *
 94                FROM ExpenseQueue
 95                WHERE conversation_handle = @dialog_handle ;
 96        END
 97
 98----------------------------------
 99
100    DECLARE @Language VARCHAR(5)
101    SET @Language=(SELECT UPPER(Language)
102                   FROM LocalizationContext)
103
104    IF @Language IS NULL
105       OR @Language=''
106    SET @Language='EN-AU'
107
108        DECLARE @Actions AS TABLE(
109                                ActionType VARCHAR(10),
110                                ExternalID UNIQUEIDENTIFIER,
111                                LinkID BIGINT,                        
112                                Enabled BIT, PRIMARY KEY(ActionType, ExternalID))
113
114--        INSERT INTO @Actions VALUES( 'Test1','{EDFA789E-A820-4D09-A50F-7F16904284B4}',0010000019,0)
115--        INSERT INTO @Actions VALUES( 'Test2','{EDFA789E-A820-4D09-A50F-7F16904284B4}',0010000110,1)
116
117
118        UPDATE @Actions SET ActionType = 'Test3' WHERE ActionType = 'Test1'  AND ExternalID = '{EDFA789E-A820-4D09-A50F-7F16904284B4}' -- Table variable is used,but no data was previously inserted
119
120        SELECT * FROM @Actions -- Table variable is used,but no data was previously inserted
121
122        DECLARE @Table1 AS dbo.TableType -- This variable has a user defined table type and will not be ignored even the IgnoreTableVariable setting is enabled.
123
124        SELECT * FROM @Table1
125
126END
127
128DECLARE @UassignedVariable int,@UassignedVariable2 int
129SELECT @UassignedVariable , @UassignedVariable2 /*IGNORE:SA0003*/   + 1 
130
131DECLARE @Delay DateTime
132WAITFOR DELAY @Delay

Analysis Results

 MessageLineColumn
1SA0003 : Variable @StartDateLookupLibId used but not previously assigned.63
2SA0003 : Variable @StartDateLookupLibId used but not previously assigned.77
3SA0003 : Variable @Title used but not previously assigned.173
4SA0003 : Variable @Birthdate used but not previously assigned.2116
5SA0003 : Variable @Incomplt used but not previously assigned.3569
6SA0003 : Variable @Buyernew used but not previously assigned.4321
7SA0003 : Variable @Table1 used but not previously assigned.12415
8SA0003 : Variable @UassignedVariable used but not previously assigned.1297
9SA0003 : Variable @Delay used but not previously assigned.13214
See Also

Other Resources