SQLServer: Exploring the differences between EXEC and sp_executesql

Summary
1. EXEC usage
2. Use of sp_executesql
MSSQL provides two types of commands for dynamic execution of SQL statements: EXEC and sp_executesql. Generally, sp_executesql is more advantageous. It provides an input and output interface, but EXEC does not. Another major benefit is that the execution plan can be reused using sp_executesql, which greatly provides the execution performance (for details in the following example ), you can also write safer code. EXEC is more flexible in some cases. Unless you have a convincing reason to use EXEC, use sp_executesql whenever possible.
1. EXEC usage
The EXEC command can be used either to execute a stored procedure or to execute a dynamic batch process. The following describes the second usage.
The following uses EXEC to demonstrate an example: Code 1
Copy codeThe Code is as follows:
DECLARE @ TableName VARCHAR (50), @ SQL NVARCHAR (MAX), @ OrderID INT;
SET @ TableName = 'Orders ';
SET @ OrderID = 10251;
SET @ SQL = 'select * from' + QUOTENAME (@ TableName) + 'where OrderID = '+ CAST (@ OrderID AS VARCHAR (10) + 'order BY ORDERID DESC'
EXEC (@ SQL );

Note: Only one string variable can be contained in EXEC brackets, but multiple variables can be concatenated. If we write EXEC as follows:
Copy codeThe Code is as follows:
EXEC ('select TOP ('+ CAST (@ TopCount as varchar (10) +') * FROM '+ QUOTENAME (@ TableName) + 'order by orderid desc ');
The SQL Compiler reports an error and the compilation fails. If we do this:
EXEC (@ SQL + @ sql2 + @ sql3 );

The compiler will pass; so the best practice is to construct the code into a variable, and then use the variable as the input parameter of the EXEC command, so that it will not be restricted;
EXEC does not provide interfaces
The interface here means that it cannot execute a batch containing a variable character. It doesn't seem to be clear at first glance. It doesn't matter. I have an instance below, you can see what it means.
Copy codeThe Code is as follows:
DECLARE @ TableName VARCHAR (50), @ SQL NVARCHAR (MAX), @ OrderID INT;
SET @ TableName = 'Orders ';
SET @ OrderID = 10251;
SET @ SQL = 'select * from' + QUOTENAME (@ TableName) + 'where OrderID = @ OrderID ORDER BY ORDERID DESC'
EXEC (@ SQL );

The key lies in the SET @ SQL statement. If we run this batch, the compiler will generate an error.
Msg 137, Level 15, State 2, Line 1
The scalar variable "@ OrderID" must be declared ".
When using EXEC, if you want to access the variable, you must concatenate the variable content into a dynamically constructed code string, such as: SET @ SQL = 'select * from' + QUOTENAME (@ TableName) + 'where OrderID = '+ CAST (@ OrderID as varchar (10) + 'order by orderid desc'
The content of the concatenation variable also has performance drawbacks. SQL Server creates a new execution plan for each query string, even if the query mode is the same. To demonstrate this, first clear the execution plan in the cache
Dbcc freeproccache (this is not the content involved in this article, you can view the ms msdn)
Http://msdn.microsoft.com/zh-cn/library/ms174283.aspx
Run code 1 three times and assign the following three values to @ OrderID: 10253. Then use the following code to query
Copy codeThe Code is as follows:
SELECT cacheobjtype, objtype, usecounts, SQL FROM sys. syscacheobjects WHERE SQL not like '% cach %' AND SQL NOT LIKE '% sys. %'

Click F5 to run. The following query result is displayed:



We can see that each execution generates a compilation, and the Execution Plan is not fully reused.
EXEC does not support output parameters except for input parameters in dynamic batch processing. By default, EXEC returns the query output to the caller. For example, the following code returns the number of all records in the Orders table
Copy codeThe Code is as follows:
DECLARE @ SQL NVARCHAR (MAX)
SET @ SQL = 'select COUNT (ORDERID) FROM Orders ';
EXEC (@ SQL );

However, if you want to return the output to the variable in the call batch, the process is not that simple. Therefore, you must use the insert exec syntax to INSERT the output to a target table, and then obtain the value from the table and assign it to the variable, as shown in the following figure:
Copy codeThe Code is as follows:
DECLARE @ SQL NVARCHAR (MAX), @ RecordCount INT
SET @ SQL = 'select COUNT (ORDERID) FROM Orders ';

Create table # T (tid int );
Insert into # t exec (@ SQL );
SET @ RecordCount = (select tid from # T)
SELECT @ RecordCount
Drop table # T

2. Use of sp_executesql
The sp_executesql command is introduced later in SQL Server than the EXEC command, which provides better support for reusing execution plans.
To make a clear comparison with EXEC, let's look at if code 1 is used to replace EXEC with sp_executesql to see if we can get the expected result.
Copy codeThe Code is as follows:
DECLARE @ TableName VARCHAR (50), @ SQL NVARCHAR (MAX), @ OrderID INT, @ sql2 NVARCHAR (MAX );
SET @ TableName = 'Orders ';
SET @ OrderID = 10251;
SET @ SQL = 'select * from' + QUOTENAME (@ TableName) + 'where OrderID = '+ CAST (@ OrderID AS VARCHAR (50) + 'order BY ORDERID DESC'
EXEC sp_executesql @ SQL

Pay attention to the last line;
It turns out that it can run;
Sp_executesql Interface
The sp_executesql command is more flexible than the EXEC command because it provides an interface that also supports output parameters as well as input parameters. This function allows you to create query strings with parameters so that execution plans can be reused better than EXEC. The composition of sp_executesql is very similar to that of stored procedures, the difference is that you are building code dynamically. It consists of: fast code, parameter declaration, and parameter value assignment. Let's take a look at its syntax.
EXEC sp_executesql
@ Stmt = <statement>, -- similar to a stored procedure subject
@ Params = <params>, -- similar to the stored procedure parameter Section
<Params assignment> -- similar to stored procedure call
The @ stmt parameter is the input dynamic batch processing. It can introduce the input or output parameters. It is the same as the main statement of the stored procedure, but it is dynamic, while the stored procedure is static, however, you can also use sp_executesql In the stored procedure;
@ Params the parameter is similar to the stored procedure header that defines the input/output parameters. In fact, the syntax of the stored procedure header is exactly the same;
@ <Params assignment> is similar to the EXEC part of the stored procedure.
To demonstrate that sp_executesql manages execution plans better than EXEC, I will use the code used in the EXEC discussed earlier.
Copy codeThe Code is as follows:
DECLARE @ TableName VARCHAR (50), @ SQL NVARCHAR (MAX), @ OrderID INT;
SET @ TableName = 'Orders ';
SET @ OrderID = 10251;
SET @ SQL = 'select * from' + QUOTENAME (@ TableName) + 'where OrderID = @ OID ORDER BY ORDERID DESC'
EXEC sp_executesql
@ Stmt = @ SQL,
@ Params = n' @ oid as int ',
@ OID = @ OrderID

Clear the execution plan in the cache before calling the code and checking the execution plan generated by the Code;
DBCC FREEPROCCACHE
Execute the above dynamic code three times. Each execution is assigned a value different from @ OrderID, and then query sys. syscacheobjects table, and pay attention to its output. The optimizer creates only one backup plan, and the plan is reused three times.
Copy codeThe Code is as follows:
SELECT cacheobjtype, objtype, usecounts, SQL FROM sys. syscacheobjects WHERE SQL not like '% cache %' AND SQL not like '% sys. %' AND SQL NOT LIKE '% sp_executesql %'

Click F5 to run the task. The result shown in the following table is displayed;

Sq_executesqlAnother powerful function related to the interface is that you can use the output parameter to return values of variables in the call batch processing. This function can be used to avoid returning data from a temporary table, so as to get more efficient code and less re-compilation. The syntax for defining and using output parameters is similar to the stored procedure. That is to say, You need to specify the OUTPUT clause when declaring the parameter. For example, the static code below demonstrates how to use the output parameter @ p in dynamic batch processing to return the value to the variable @ I in the external batch processing.
Copy codeThe Code is as follows:
DECLARE @ SQL as nvarchar (12), @ I AS INT;
SET @ SQL = n' SET @ p = 10 ';
EXEC sp_executesql
@ Stmt = @ SQL,
@ Params = n' @ p as int output ',
@ P = @ I OUTPUT
SELECT @ I

This code returns output 10
The above is the main difference between EXEC and sp_executesql. If you think something is wrong or you are not clear about it, Please also comment out ^_^