The difference between EXEC and sp_executesql

Summary:

MSSQL provides us with two commands for dynamically executing SQL statements: EXEC and sp_executesql. Typically, sp_executesql is more advantageous because it provides an interface for input and output, and the ability to reuse execution plans, greatly improving execution efficiency, without causing SQL injection and more security, which exec does not have, which exec typically uses to execute stored procedures. So, unless there is a good reason, try to use sp_executesql when executing dynamic SQL.

Examples of usage:

-----------EXEC:

DECLARE @TableName VARCHAR, @Sql NVARCHAR (MAX), @OrderID INT;
SET @TableName = ' Orders ';
SET @OrderID = 10251;
SET @sql = ' SELECT * from ' +quotename (@TableName) + ' WHERE OrderID = ' +cast (@OrderID as VARCHAR) + ' ORDER by OrderID DES C
EXEC (@sql);

Note: Be aware that you should use Exec with parentheses and only one string variable in parentheses, or a concatenation of multiple strings, for example:

EXEC (@[email protected][email protected]);

But the following will be an error.

EXEC (' SELECT TOP (' + CAST (@TopCount as VARCHAR) + ') * from ' +quotename (@TableName) + ' ORDER by ORDERID DESC ');

So the best practice is to construct all the code into a string variable and then exec (the variable).

-----------sp_executesql:

First look at the syntax of sp_executesql:

stmt [  {@params =N ' @[out | OUTPUT [,... N]  } {,@param1'  value1  ,... ] }]

[ @stmt=] statement

A Unicode string that contains a Transact-SQL statement or batch.@stmt must be a Unicode constant or a Unicode variable.More complex Unicode expressions are not allowed (for example, using the + operator to concatenate two strings).Character constants are not allowed. N   As the prefix,   in 64-bit servers, the string size is limited to 2 GB,   nvarchar (max)   maximum size.  

[@params =] n ' @parameter_namedata_type [,... N] '
A string that contains the definition of all parameters embedded in the @stmt. The string must be a Unicode constant or a Unicode variable. Each parameter definition consists of a parameter name and a data type. Each parameter specified in the @stmt must be defined in the @params. If the Transact-SQL statement or batch in the @stmt does not contain parameters, you do not need to use the @params. The default value for this parameter is NULL.

[ @param1=] 'value1'

The value of the first parameter defined in the argument string.The value can be a Unicode constant, or it can be a Unicode variable.   parameter values must be supplied for each parameter contained in the @stmt. These values are not required if the Transact-SQL statement or batch in the @stmt has no parameters.

Note: Parameter passing can either choose ' @name = value ' form or write ' value ' directly, but once the ' @name = value ' form is used, all subsequent arguments must be passed in the form of ' @name = value '. If you need to pass the value of a parameter, you need to add output after [@param1=] 'value1'.

Examples are as follows:

DECLARE @OUT_Nums int, @IN_Score int, @Sql NVARCHAR (MAX)
SET @IN_Score = 90
SET @sql = ' SELECT @Nums =count (1) from T_student WHERE score >= @Score '
EXEC sp_executesql @sql, N ' @Nums int out, @Score int ', @OUT_Nums OUTPUT, @IN_Score
SELECT @OUT_Nums as ' number '

The difference between EXEC and sp_executesql