DF016: EXEC(@sql) is used.

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

While EXEC remains available for backward compatibility, it presents significant risks and limitations, particularly with dynamic SQL.

Why prefer sp_executesql:

• Improved security: Reduces the risk of SQL injection by allowing parameterized queries with proper input handling.

• Better performance: Promotes query plan reuse, which can reduce compilation overhead and speed up execution.

Recommendation:

Replace EXEC(@sql) with sp_executesql to ensure safer and more efficient execution of dynamic SQL statements.

Noncompliant code example

IF (SELECT Value FROM dbo.DemoTable WHERE Code = 'Demo') = 1
SELECT 'Data found'
GO
 
SELECT Code
,(SELECT Value FROM dbo.DemoTable WHERE Code = 'Demo')
FROM dbo.DemoTable
GO

Compliant solution

IF (SELECT TOP(1) Value FROM dbo.DemoTable WHERE Code = 'Demo') = 1
SELECT 'Data found'
GO
 
SELECT Code
    ,(SELECT TOP(1) Value FROM dbo.DemoTable WHERE Code = 'Demo')
FROM dbo.DemoTable
GO
 
IF (SELECT max(Value) FROM dbo.DemoTable WHERE Code = 'Demo') = 1
SELECT 'Data found'
GO
 
SELECT Code
    ,(SELECT max(Value) Value FROM dbo.DemoTable WHERE Code = 'Demo')
FROM dbo.DemoTable
GO