Introduction to Exec and sp_executesql usage in SQL Server

SQL Server Introduction to the use of exec and sp_executesql

MSSQL There are two commands for dynamically executing SQL statements, exec and sp_executesql, and generally, sp_executesql has the advantage of providing an input-output interface, and exec does not. One of the biggest benefits is the ability to reuse the execution plan with sp_executesql, which provides execution performance (which I'll explain in more detail in a later example), and can also write more secure code. EXEC will be more flexible in some cases. Unless you have compelling reasons to use Exec, use sp_executesql on the side as much as possible.

1 , EXEC 's use

EXEC There are two uses of commands, one is to execute one stored procedure, and the other is to perform a dynamic batch processing. The second usage is described below.

Here's an example of using exec first , code 1

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

SET @TableName = ' Orders ';

SET @OrderID = 10251;

SET @sql = ' SELECT * from ' +quotename (@TableName) + ' Whereorderid = ' +cast (@OrderID as VARCHAR) + ' ORDER by OrderID DESC ‘

EXEC (@sql);

Note: The exec brackets here allow only one string variable to be included, but you can concatenate multiple variables if we write exec like this:

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

SQL the compiler will error, compile does not pass, and if we do:

EXEC (@SQL+ @sql2 + @sql3);

The compiler will pass;

so the best thing to do is to construct the code into a variable, and then use that variable as an input parameter to the EXEC command so that it is not restricted;

EXEC No interface available

The interface here means that it can't execute a batch with a variable character, and here at first glance it doesn't seem to understand, it doesn't matter, I have an example below, you know what the meaning is.

DECLARE @TableName VARCHAR (+), @ SQL NVARCHAR (MAX), @OrderID INT ;

SET @TableName = ' Orders ' ;

SET @OrderID = 10251;

SET @ SQL = ' SELECT * from ' +quotename (@TableName) + ' WHEREOrderID = @OrderID ORDER by OrderID DESC '

EXEC (@sql);

The key is in the set @sql this sentence, if we run this batch, the compiler will produce an error

MSG 137, Level A, state 2, line 1 the scalar variable "@OrderID" must be declared.

When using EXEC , if you want to access variables, you must concatenate the variable contents into the dynamically constructed code string, such as: SET @sql = ' SELECT * from ' +quotename (@TableName) + ' WHERE OrderID = ' + CAST (@OrderID as VARCHAR) + ' ORDER by OrderID DESC '

the contents of a series variable also have some drawbacks in performance. SQL Server creates a new execution plan for each query string, even if the query pattern is the same. To demonstrate this, first empty the execution plan in the cache

DBCC Freeproccache ( This is not what this article covers, you can view Ms 's MSDN)

http://msdn.microsoft.com/zh-cn/library/ms174283.aspx  

Run code 1 3 times, giving the following 3 values to @orderid, respectively, 10251,10252,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 and the following query results will appear:

We can see that every execution has to be compiled once, and the execution plan is not fully reused.

EXEC In addition to not supporting input parameters in dynamic batching, he does not support output parameters. By default, EXEC returns the output of the query to the caller. For example, the following code returns the number of 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 a variable in the call batch, things are not that simple. To do this, you must use the Insert EXEC syntax to insert the output into a target table and then assign the value to the variable from the table, just like this:

DECLARE @ SQL NVARCHAR (MAX), @RecordCount INT

SET @ SQL = ' SELECT COUNT (ORDERID) from Orders ' ;

CREATE TABLE #T (TIDINT);

INSERT into #T EXEC (@sql);

SET @RecordCount = (SELECT TID from #T)

SELECT @RecordCount

DROP TABLE #T

2 , the use of sp_executesql

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

to make a stark contrast with exec, let's see if we replace exec with sp_executesql in code 1 to see if we get the results we want.

DECLARE @TableName VARCHAR (+), @ SQL NVARCHAR (MAX), @OrderID INT , @sql2 NVARCHAR (MAX);

SET @TableName = ' Orders ' ;

SET @OrderID = 10251;

SET @ SQL = ' SELECT * from ' +quotename (@TableName) + ' WHERE OrderID = ' + CAST (@OrderIDasVARCHAR)+ ' ORDER by Orderiddesc '

EXEC sp_executesql @sql

Note the last line;

It turns out that it can run;

sp_executesql provide interface

sp_executesql The command is more flexible than the exec command because it provides an interface that supports output parameters as well as support input parameters. This feature allows you to create query strings with parameters so that you can reuse execution plans better than exec, and the composition of sp_executesql is very similar to stored procedures, except that you are dynamically building code. Its composition includes: Fast Code, parameter declaration part, parameter assignment part. If you say so much, look at its syntax.

EXEC sp_executesql

@stmt = <statement>,-- similar to stored procedure body

@params = <params>,--- Similar stored procedure parameters section

<params assignment>-- similar to stored procedure calls

@stmt A parameter is a dynamic batch of inputs that can introduce input or output parameters, just like the body statement of a stored procedure, except that it is dynamic and that the stored procedure is static, but you can also use sp_executesql in stored procedures;

@params The parameters are similar to the stored procedure headers that define the input/ output parameters, and are actually identical to the syntax of the stored procedure header;

@<params assignment> Similar to the exec portion of the calling stored procedure .

to illustrate that sp_executesql 's management of execution plans is better than exec, I'll use the code that was used to discuss exec earlier.

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

2: SET @TableName = ' Orders ';

3: SET @OrderID = 10251;

4: SET @sql = ' SELECT * from '+quotename (@TableName) +' WHERE OrderID = @OID ORD ER by ORDERID DESC '

5: EXEC sp_executesql

6: @stmt = @sql,

7: @params = N' @OID as INT ',

8: @OID = @OrderID

Empty the execution plan in the cache before calling the code and checking the execution plan generated by it;

DBCC Freeproccache

execute the above dynamic code 3 times, each execution will give @orderid different values, then query the Sys.syscacheobjects table, and note its output, the optimizer only created a standby plan, and the plan was reused 3 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, you will see the results shown in the following table;

Sq_executesql another powerful feature associated with its interface is that you can use the output parameter to return a value for a variable in the call batch. This feature avoids using temporary tables to return data, 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 an OUTPUT clause when declaring a parameter . For example, the following static code simply demonstrates how to use the output parameter @p from a dynamic batch to return a value to a variable @i in an external batch.

DECLARE @sql as NVARCHAR, @i as INT;

SET @sql = N ' SET @p = Ten ' ;

EXEC sp_executesql

@stmt = @sql,

@params = N' @p as INT OUTPUT ',

@p= @i OUTPUT

SELECT @i

The code returns the output 10

Introduction to Exec and sp_executesql usage in SQL Server