SA0077 : Avoid executing dynamic code using EXECUTE statement

Avoid executing dynamic code using EXECUTE statement

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.

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

Rule has no parameters.

The rule does not need Analysis Context or SQL Connection.

Design Rules, Performance Rules, Security Rules

There is no additional info for this rule.

 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

Other Resources