The difference between sqlserver:exec and sp_executesql

Reprinted from:

Xing Bai time: 2008-11-2-22:30 website: Http://xbf321.cnblogs.com

Http://www.cnblogs.com/xbf321/archive/2008/11/02/1325067.html

Summary

Use of 1,exec

Use of 2,sp_executesql

MSSQL provides us with two commands for dynamically executing SQL statements, namely 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.

Use of 1,exec

The EXEC command has two uses, one for executing a stored procedure, and the other for performing a dynamic batch processing. The second usage is described below.

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

VARCHAR (+), @Sql NVARCHAR (INT; 

' Orders ';

SET @OrderID = 10251;

SET @' SELECT * from ' +quotename (@TableName) +' WHERE OrderID = ' +VARCHAR (Ten) +' ORDER by OrderID D ESC '   

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 (VARCHAR) +') * from ' +quotename (@TableName) +' ORDER by ORDERID DESC ');  

The SQL compiler will make an error, the compilation does not pass, and if we do:

EXEC (@[email protected][email protected]);

The compiler will pass; it is best 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 does not provide an interfaceThe 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.

VARCHAR (+), @Sql NVARCHAR (INT; 

' Orders ';

SET @OrderID = 10251;

SET @' SELECT * from ' +quotename (@TableName) +OrderID = @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, the following query results appear:  

We can see that every execution is generated once, and the execution plan is not fully reused.

Exec does not support output parameters in addition to input parameters in dynamic batching. 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);   

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)  span>  

select @RecordCount  

  DROP table #T   

Use of 2,sp_executesql

The sp_executesql command introduced in SQL Server later than the EXEC command, which mainly 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.

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

' Orders ';

SET @OrderID = 10251;

SET @' WHERE OrderID = ' +' ORDER by OrderID DESC ' 

EXEC sp_executesql @sql

Note the last line;

It turns out that it can run;

Sp_executesql provides interface

The sp_executesql 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 a stored procedure body

@params = <params>,--similar to the stored Procedure parameters section

<params assignment>--similar to a stored procedure call

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

The @params parameter is similar to the stored procedure header that defines the input/output parameters, and is actually exactly the same as the syntax of the stored procedure header;

@<params assignment> is similar to the exec part of calling stored procedures.

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, @sql NVARCHAR (MAX), @OrderID INT;

   2:  ' Orders ';

   3:  SET @OrderID = 10251;

   4:  ' WHERE OrderID = @OID ORDER 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

'%sp_executesql% '

Click F5 to run, you will see the results shown in the following table;

Another powerful feature of Sq_executesql 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 ';

    @stmt = @sql,

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

    @p = @i OUTPUT

SELECT @i

The code returns the output

The difference between sqlserver:exec and sp_executesql