SA0077 : Avoid executing dynamic code using EXECUTE statement |
 |
The topic describes the SA0077 analysis rule.
MessageAvoid executing dynamic code using EXECUTE statement
DescriptionThe 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.
How to fixUse `sp_executesql` stored procedure to execute dynamic SQL code instead of the EXECUTE statement
ScopeThe rule has a Batch scope and is applied only on the SQL script.
ParametersRule has no parameters.
RemarksThe rule does not need Analysis Context or SQL Connection.
Effort To FixCategoriesDesign Rules, Performance Rules, Security Rules
Additional InformationThere is no additional info for this rule.
Example Test SQL|
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