What is the difference between EXEC and sp_executesql?

The use of MSSQL by sp_executesql provides two types of commands for dynamic execution of SQL statements: EXEC and sp_executesql. Generally, sp_executesql is more advantageous and provides an input/output interface, EXEC does not. Another major benefit is that the execution plan can be reused using sp_executesql, which greatly provides the execution performance (

The use of MSSQL by sp_executesql provides two types of commands for dynamic execution of SQL statements: EXEC and sp_executesql. Generally, sp_executesql is more advantageous and provides an input/output interface, EXEC does not. Another major benefit is that the execution plan can be reused using sp_executesql, which greatly provides the execution performance (

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.

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
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:

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, then, use the variable as the input parameter of the EXEC command. The exam prompts that the variable 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.

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)

　　

Run code 1 three times and assign the following three values to @ OrderID: 10253. Then use the following code to query

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

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:

DECLARE @ SQL NVARCHAR (MAX), @ RecordCount INTSET @ SQL = 'select COUNT (ORDERID) FROM Orders '; CREATE TABLE # T (TID INT ); insert into # t exec (@ SQL); SET @ RecordCount = (SELECT TID FROM # T) SELECT @ RecordCountDROP TABLE # T2, sp_executesql usage

The sp_executesql command is introduced later in SQL Server than the EXEC command, which provides better support for reusing execution plans.