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

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

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

Want to Find out More?

Overview

Overview

Take a quick tour to learn all about the key benefits delivered by dbForge Studio for SQL Server.
All Features

All features

Get acquainted with the rich features and capabilities of the Studio in less than 5 minutes.
Request a demo

Request a demo

If you consider employing the Studio for your business, request a demo to see it in action.
Ready to start using dbForge Studio for SQL Server?
Download a free 30-day trial