SA0077 : Avoid executing dynamic code using EXECUTE statement |
![]() |
The unnecessary use of EXECUTE for dynamic SQL can lead to inefficiencies and security risks.

Dynamic SQL execution using the EXECUTE statement in SQL Server is a common approach but can lead to several issues. It lacks parameterization, which increases the risk of SQL injection attacks and can diminish query performance by reducing the likelihood of reusing execution plans.
For example:
1-- Problematic dynamic SQL execution 2EXECUTE('SELECT * FROM Employees WHERE EmployeeID = ' + @EmployeeID);
This approach is problematic because:
-
It exposes the query to SQL injection attacks as the parameters are concatenated directly into the SQL string.
-
It is less efficient because SQL Server has limited ability to reuse execution plans, impacting query performance.

Use sp_executesql to execute dynamic SQL statements securely and efficiently, minimizing the risks associated with SQL injection and improving query performance through parameterization.
Follow these steps to address the issue:
-
Rewrite dynamic SQL queries to use sp_executesql instead of EXECUTE. This allows for parameterized queries that are safer and more efficient.
-
Define the query parameters explicitly in a @Params variable to provide data type information and enable plan reuse.
-
Call sp_executesql with the query string, parameter definition, and actual parameter values.
For example:
1-- Example using sp_executesql for dynamic SQL execution 2DECLARE @sql NVARCHAR(MAX); 3DECLARE @Params NVARCHAR(MAX); 4DECLARE @EmployeeID INT; 5 6SET @sql = N'SELECT * FROM Employees WHERE EmployeeID = @EmployeeID'; 7SET @Params = N'@EmployeeID INT'; 8 9-- Safe and efficient execution 10EXEC sp_executesql @sql, @Params, @EmployeeID = @EmployeeID;

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.

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 ) |

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 |
