SA0077 : Avoid executing dynamic code using EXECUTE statement

The topic describes the SA0077 analysis rule.

Message

Avoid executing dynamic code using EXECUTE statement

Description

The rule checks for EXECUTE statements which use dynamically generated SQL code.

To execute a string, it is recommend the use of sp_executesql stored procedure instead of the EXECUTE statement.

Because this stored procedure supports parameter substitution, sp_executesql is more versatile than EXECUTE; and because sp_executesql generates execution plans that are more likely to be reused by SQL Server, sp_executesql is more efficient than EXECUTE.

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.

Categories

Design Rules, Performance Rules, Security Rules

Additional Information

There is no additional info for this rule.

Example Test Script
SQL
 1DECLARE  @TableName AS VARCHAR( 100 )
 2DECLARE  @SQL AS VARCHAR( 1000 )
 3
 4SET @TableName = 'MyTable'
 5
 6SELECT  @SQL = 'CREATE TABLE ' + @TableName + '('
 7
 8SELECT  @SQL = @SQL + 'ID int NOT NULL PRIMARY KEY, FIELDNAME varchar(10))'
 9
10EXEC(  @SQL )
11
12EXECUTE(  @SQL )

Analysis Results

  Message Line Column
1 SA0077 : Avoid executing dynamic code using EXECUTE statement. 10 0
2 SA0077 : Avoid executing dynamic code using EXECUTE statement. 12 0
See Also

Other Resources