Shulou(Shulou.com)05/31 Report--

This article will explain in detail what is the difference between EXEC and sp_executesql in SQLServer. The content of the article is of high quality, so the editor will share it with you for reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

Abstract 1the use of MSSQL exec provides us with two commands for dynamic execution of SQL statements, namely EXEC and sp_executesql;. Generally speaking, sp_executesql has more advantages, it provides input and output interface, but EXEC does not. One of the biggest benefits is the ability to reuse execution plans with sp_executesql, which greatly provides execution performance (which I'll explain in more detail in a later example) and allows you to write more secure code. EXEC can be more flexible in some cases. Unless you have a compelling reason to use EXEC, try to use sp_executesql. There are two ways to use the EXEC command in the use of EXEC exec, one is to execute a stored procedure, and the other is to execute a dynamic batch. The following are all the second uses. Let's use EXEC to demonstrate an example. Code 1 copies the code 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 is allowed in EXEC parentheses here, but you can concatenate multiple variables. If we write EXEC like this: copy the code as follows: EXEC ('SELECT TOP (' + CAST (@ TopCount AS VARCHAR (10)) +') * FROM'+ QUOTENAME (@ TableName) + 'ORDER BY ORDERID DESC'); the SQL compiler will report an error and the compiler will fail, and 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 take the variable as the input parameter of the EXEC command, so it will not be restricted; EXEC does not provide an interface here means that it cannot execute a batch containing a variable character, which at first glance does not seem to understand, never mind, I have an example below, you will know what it means at first glance. The copy code is as follows: DECLARE @ TableName VARCHAR (50), @ Sql NVARCHAR (MAX), @ OrderID INT;SET @ TableName = 'Orders';SET @ OrderID = 10251X set @ sql =' SELECT * FROM'+ QUOTENAME (@ TableName) + 'WHERE OrderID = @ OrderID ORDER BY ORDERID DESC'EXEC (@ sql)

The key is in the sentence SET @ sql. If we run this batch, the compiler will generate the error Msg 137, Level 15, State 2, Line 1 must declare the scalar variable "@ OrderID". When using EXEC, if you want to access variables, you must concatenate the contents of variables into dynamically built code strings, such as: SET @ sql = 'SELECT * FROM' + QUOTENAME (@ TableName) + 'WHERE OrderID =' + CAST (@ OrderID AS VARCHAR (10)) + 'ORDER BY ORDERID DESC' concatenated variables also have performance drawbacks. SQL Server creates a new execution plan for each query string, even if the query pattern is the same. To demonstrate this, first clear the execution plan DBCC FREEPROCCACHE in the cache (this is not covered in this article, you can check MS's MSDN) http://msdn.microsoft.com/zh-cn/library/ms174283.aspx runs code 1 three times, assigning the following three values to @ OrderID, 10251mem10252j10253. Then query the copy code using the following code: SELECT cacheobjtype,objtype,usecounts,sql FROM sys.syscacheobjects WHERE sql NOT LIKE'% cach%' AND sql NOT LIKE'% sys.%'

Click F5 to run, and you will see the query results shown in the figure below: we can see that each execution produces a compilation, and the execution plan is not fully reused. EXEC does not support output parameters except for input parameters in dynamic batches. By default, EXEC returns the output of the query to the caller. For example, the following code returns all the records in the Orders table. The copy 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 a variable in the calling batch, it's not that simple. To do this, you must insert the output into a target table using INSERT EXEC syntax, then get the value from the table and assign it to the variable, like this: copy the code as follows: 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 # T

The use of the sp_executesql command in SQL Server is a little later than the EXEC command, which mainly provides better support for reusing the execution plan. To make a sharp contrast with EXEC, let's see if we replace EXEC with sp_executesql with the code in code 1, and see if we get the desired result to copy the code 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

Note the last line; it turns out to work; the sp_executesql provides interface sp_executesql command is more flexible than the EXEC command because it provides an interface, and the interface and support input parameters also support output parameters. This feature 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 stored procedures, except that you build code dynamically. Its composition includes: code fast, parameter declaration part, parameter assignment part. Having said that, let's take a look at its syntax EXEC sp_executesql@stmt =,-- similar to the stored procedure body @ params =,-- similar to the stored procedure parameter part-- similar to the stored procedure call @ stmt parameter is a dynamic batch of input parameters, which can introduce input parameters or output parameters, just like the body statement of the stored procedure, except that it is dynamic, while the stored procedure is static. But you can also use sp_executesql in stored procedures The @ params parameter is similar to the stored procedure header that defines the input / output parameters, but actually has exactly the same syntax as the stored procedure header @ is similar to calling the EXEC part of a stored procedure. To show that sp_executesql is better at managing execution plans than EXEC, I'll use the code I used when I discussed EXEC earlier. The copy 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 it generates DBCC FREEPROCCACHE executes the above dynamic code three times, each time assigning a different value to @ OrderID, then queries the sys.syscacheobjects table and notices its output. The optimizer creates only one backup plan, and the three replication codes that are reused for the plan are 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, and the results shown in the following table will appear.

Another powerful feature of sq_executesql related to its interface is that you can use output parameters to return values for variables in the calling batch. Using this feature, you can avoid returning data with temporary tables, resulting in more efficient code and less recompilation. The syntax for defining and using output parameters is similar to stored procedures. That is, you need to specify the OUTPUT clause when declaring parameters. For example, the following static code simply demonstrates how to use the output parameter @ p to return the value to the variable @ I in the external batch from the dynamic batch. The copy code is as follows: DECLARE @ sql AS NVARCHAR (12), @ I AS INT;SET @ sql = N' SET @ p = 10 percent exec sp_executesql @ stmt = @ sql, @ params = Nabilp AS INT OUTPUT', @ p = @ I OUTPUTSELECT @ I

So much for sharing what is the difference between EXEC and sp_executesql in SQLServer. I hope the above content can be helpful to you and learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.