Click or drag to resize

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

Additional Information

There is no additional info for this rule.

Example Test Script
 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

 MessageLineColumn
1SA0077 : Avoid executing dynamic code using EXECUTE statement.100
2SA0077 : Avoid executing dynamic code using EXECUTE statement.120
See Also

Other Resources