SA0020 : Always use a column list in INSERT statements

The topic describes the SA0020 analysis rule.

Message

Always use a column list in INSERT statements

Description

This rule checks objects for use of INSERT statements without explicit specification of target column list.

When inserting into a table or view, it is recommended that the target column_list be explicitly specified. This results in more maintainable code and helps in avoiding problems when the table structure changes (like adding or dropping a column).

Scope

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

Parameters
Name Description Default Value
IgnoreTempTargetTables

Ignore targets that are temporary tables or table variables.

yes

Remarks

The rule does not need Analysis Context or SQL Connection.

Categories

Design Rules, Code Smells

Additional Information

There is no additional info for this rule.

Example Test Script
SQL
 1CREATE PROCEDURE HumanResources.uspGetEmployees 
 2    @LastName nvarchar(50), 
 3    @FirstName nvarchar(50),
 4        @JobTitle nvarchar(50),
 5        @Department nvarchar(50)
 6AS 
 7
 8    SET NOCOUNT ON;
 9
10        -- Target columns are not provided
11        INSERT INTO HumanResources.vEmployeeDepartment
12        VALUES (@FirstName,@LastName,@JobTitle,@Department)
13
14        INSERT INTO #EmployeeDepartment
15        VALUES (@FirstName,@LastName,@JobTitle,@Department)
16
17        -- Target columns are explicitly provided
18        INSERT INTO HumanResources.vEmployeeDepartment(FirstName, LastName, JobTitle, Department)
19        VALUES (@FirstName,@LastName,@JobTitle,@Department)
20
21        INSERT INTO HumanResources.vEmployeeDepartment
22        VALUES (@FirstName,@LastName,@JobTitle,@Department) /*IGNORE:SA0020*/

Analysis Results

  Message Line Column
1 SA0020 : Always use a column list in INSERT statements. 11 1
See Also

Other Resources