DF016: EXEC(@sql) is used.

Last modified: December 25, 2024

The topic describes the DF016 T-SQL code analysis rule.

Category

PERFORMANCE

Message

EXEC(@sql) is used.

Description

It is not recommended to use EXEC(@sql) to run dynamic SQL. Use the sp_executesql procedure instead.

Additional information

Parameters can be provided by specifying a value or by using the syntax @parameter_name=value. It is important to note that parameters are not included in a transaction. Therefore, if a parameter is modified within a transaction that is subsequently rolled back, the parameter value is not reverted to its previous state. Instead, the value returned to the caller always reflects the value when the module completes execution.

Noncompliant code example

EXEC ('select * from dbo.Customers')

Compliant solution

EXEC sys.sp_executesql N'select * from dbo.Customers'