SA0267 : Table variable is used before it has any data inserted

The topic describes the SA0267 analysis rule.

Message

Table variable is used before it has any data inserted

Description

The rule checks T-SQL code for create temporary tables statements, where the particular table is never inserted or is referenced as source table before previously appearing as target table in INSERT,SELECT INTO or MERGE statements, or as output table in OUTPUT INTO clause.

How to fix

Review the table variable usage and ensure the table has any rows inserted before being referenced as a source table.

Scope

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

Parameters

Rule has no parameters.

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix
5 minutes per issue.
Categories

Design Rules, Bugs

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
 1DECLARE @mail TABLE(      
 2                        toAddress NVARCHAR( 100 ) ,
 3                        fromAddres NVARCHAR( 100 ) ,
 4                        subject NVARCHAR( 256 ) ,
 5                        body NVARCHAR( 4000 )
 6                   );
 7
 8SELECT * FROM @mail
 9
10DELETE FROM @mail
11UPDATE @mail set  toAddress =''
12
13INSERT INTO @mail
14SELECT * from mail 
15
16SELECT * FROM @mail

Analysis Results
  Message Line Column
1 SA0267 : Table variable is used before it has any data inserted. 8 14
2 SA0267 : Table variable is used before it has any data inserted. 10 12
3 SA0267 : Table variable is used before it has any data inserted. 11 7
See Also

Other Resources