EXEC and sp in sqlserver

Description of EXEC and sp_executesql in SQL Server 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/output interface, while EXEC does not. Another major benefit is that the execution plan can be reused using sp_executesql,

Description of EXEC and sp_executesql in SQL Server 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/output interface, while EXEC does not. Another major benefit is that the execution plan can be reused using sp_executesql,

Introduction to EXEC and sp_executesql in sqlserver

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

DECLARE @ TableName VARCHAR (50), @ SqlNVARCHAR (MAX), @ OrderID INT;

SET @ TableName = 'Orders ';

SET @ OrderID = 10251;

SET @ SQL = 'select * from' + QUOTENAME (@ TableName) + 'whereorderid = '+ 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 ASVARCHAR (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;

Therefore, 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.

DECLARE @ TableNameVARCHAR (50), @ SQL NVARCHAR (MAX), @ OrderIDINT;

SET @ TableName = 'Orders ';

SET @ OrderID = 10251;

SET @ SQL = 'select * from' + QUOTENAME (@ TableName) + 'whereOrderID = @ OrderIDOrder 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 must declare the scalar variable "@ OrderID ".

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 newExecution Plan,This is true 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

SELECTcacheobjtype, objtype, usecounts, sqlFROM sys. syscacheobjectsWHEREsqlNOTLIKE '% cach % 'andsqlnotlike' % 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), @ RecordCountINT

SET @ SQL = 'select COUNT (ORDERID) FROM Orders ';

CREATETABLE # T (TIDINT );

Insert into # t exec (@ SQL );

SET @ RecordCount = (select tidfrom # T)

SELECT @ RecordCount

DROPTABLE # 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.

DECLARE @ TableNameVARCHAR (50), @ SQL NVARCHAR (MAX), @ OrderIDINT, @ sql2 NVARCHAR (MAX );

SET @ TableName = 'Orders ';

SET @ OrderID = 10251;

SET @ SQL = 'select * from' + QUOTENAME (@ TableName) + 'where OrderID = '+ CAST (@ OrderIDASVARCHAR (50) + 'order BY ORDERIDDESC'

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 = , -- Similar to a stored procedure subject

@ Params = , -- Similar to stored procedure parameters

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

@ 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.

1: DECLARE @ TableName VARCHAR (50), @ sqlNVARCHAR (MAX), @ OrderID INT;

2: SET @ TableName = 'Orders ';

3: SET @ OrderID = 10251;

4: SET @ SQL = 'select * from' + QUOTENAME (@ TableName) + 'where OrderID = @ OID ORDER BY ORDERID DESC'

5: EXEC sp_executesql

6: @ stmt = @ SQL,

7: @ params = n' @ oid as int ',

8: @ 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.

SELECT cacheobjtype, objtype, usecounts, sqlFROM 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;

Another powerful function of sq_executesql related to its interface is that you can use output parameters 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.

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