Skip to content

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:

-- Problematic dynamic SQL execution
EXECUTE('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:

1.Rewrite dynamic SQL queries to use sp_executesql instead of EXECUTE . This allows for parameterized queries that are safer and more efficient.

2.Define the query parameters explicitly in a @Params variable to provide data type information and enable plan reuse.

3.Call sp_executesql with the query string, parameter definition, and actual parameter values.

For example:

-- Example using sp_executesql for dynamic SQL execution
DECLARE @sql NVARCHAR(MAX);
DECLARE @Params NVARCHAR(MAX);
DECLARE @EmployeeID INT;
SET @sql = N'SELECT * FROM Employees WHERE EmployeeID = @EmployeeID';
SET @Params = N'@EmployeeID INT';
-- Safe and efficient execution
EXEC 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.

3 hours per issue.

Design Rules, Performance Rules, Security Rules

There is no additional info for this rule.

DECLARE @TableName AS VARCHAR( 100 )
DECLARE @SQL AS VARCHAR( 1000 )
SET @TableName = 'MyTable'
SELECT @SQL = 'CREATE TABLE ' + @TableName + '('
SELECT @SQL = @SQL + 'ID int NOT NULL PRIMARY KEY, FIELDNAME varchar(10))'
EXEC( @SQL )
EXECUTE( @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

Analysis Rules