The difference between sqlserver:exec and sp_executesql

MSSQL provides us with two commands for dynamically executing SQL statements, namely EXEC and sp_executesql. Typically, sp_executesql has the advantage of providing an input-output interface, while 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 as much as possible.

Use of 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

int 2 ; Set '  ' as varchar '; EXEC (@SQL);

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.

int 1 ; Set ' SELECT * FROM dbo. donators WHERE Donatorid = @DonatorId'; EXEC (@SQL);

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

137  the 2 1  "@DonatorId".

When using exec, if you want to access variables, you must concatenate the variable contents into a dynamically constructed code string, such as:

Set '  ' as varchar ';

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)

Run code 1 3 times, giving the following 3 values to @orderid, respectively, 2,3,4. Then use the following code to query

' %cach% ' ' %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 does not support output parameters except for input parameters that are not supported 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

' SELECT COUNT (donatorid) from Donators ' ; 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:

' SELECT COUNT (donatorid) from Donators '  = (SELECT TID from #T); SELECT @RecordCount;D rop TABLE #T;

Use of 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.

int 2 ; Set '  ' as varchar '; EXEC sp_executesql @SQL; 

Pay attention to the last line; it turns out to work;

Sp_executesql provides interface

The sp_executesql command is more flexible than the EXEC command because it provides an interface that supports both input parameters and output parameters. This feature allows you to create query strings with parameters so that you can reuse execution plans better than exec. Its composition includes: code block, parameter declaration part, parameter assignment part. If you say so much, look at its syntax.

= <statement>,--= <params;,-- declaration parameters <params assignment>--parameter assignment

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.

int 2 ;D eclare @DonatorName varchar (a); Set ' SELECT * FROM dbo. donators WHERE Donatorid = @id'; EXEC sp_executesql    = @SQL,    = N'@id as int',    = @DonatorId;

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

' %cache% ' ' %sys.% ' ' %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 an 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.

int 0 ; Set ' SELECT @n = MAX (Donatorid) from dbo. Donators'; EXEC sp_executesql    = @SQL,    = N'@n as int output',     = @count output; SELECT @count;

The above is the main difference between exec and sp_executesql, if you crossing feel what is wrong or express unclear, please also point out ^_^

The difference between sqlserver:exec and sp_executesql