Summary
The use of 1,exec
The use of 2,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 that exec does not. One of the biggest benefits is the ability to reuse the execution plan with sp_executesql, which provides a great performance boost (as I'll explain 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, do not use sp_executesql as much as possible.
The use of 1,exec
The EXEC command has two uses, one is to execute a stored procedure, the other is to perform a dynamic batch. The second use is the following.
Let's use exec to demonstrate an example, code 1
Copy Code code as follows:
DECLARE @TableName VARCHAR (m), @Sql NVARCHAR (MAX), @OrderID INT;
SET @TableName = ' Orders ';
SET @OrderID = 10251;
SET @sql = ' SELECT * from ' +quotename (@TableName) + ' WHERE OrderID = ' +cast (@OrderID as VARCHAR) + ' ORDER by OrderID DES C
EXEC (@sql);
Note: Only one string variable is allowed in the exec brackets here, but you can concatenate multiple variables if we write exec:
Copy Code code as follows:
EXEC (' SELECT top (' + CAST (@TopCount as VARCHAR) + ') * +quotename (@TableName) + ' ORDER by ORDERID DESC ');
The SQL compiler will have an error, the compilation does not pass, and if we do:
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 an input parameter to the EXEC command, so that it is not restricted;
exec does not provide an interface
The interface here is that it cannot perform a batch with a variable character, which at first glance does not seem to understand, does not matter, I have an example below, you know what the meaning.
Copy Code code as follows:
DECLARE @TableName VARCHAR (m), @Sql NVARCHAR (MAX), @OrderID INT;
SET @TableName = ' Orders ';
SET @OrderID = 10251;
SET @sql = ' SELECT * from ' +quotename (@TableName) + ' WHERE 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, State 2, line 1
the scalar variable "@OrderID" must be declared.
When using exec, if you want to access a variable, you must concatenate the contents of the variable into a 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 the 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 empty the execution plan in the cache
DBCC Freeproccache (This is not what this article covers, you can view Ms MSDN)
Http://msdn.microsoft.com/zh-cn/library/ms174283.aspx
Run the Code 1 3 times, assigning the following 3 values to @orderid respectively, 10251,10252,10253. Then use the following code to query
Copy Code code as follows:
SELECT cacheobjtype,objtype,usecounts,sql from sys.syscacheobjects WHERE sql isn't like '%cach% ' and ' sql not like '%sys.% '
If you click F5 to run, you will see the following query results as shown in the figure:
We can see that every time we execute a compilation, 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 the number of records in the Orders table
Copy Code code 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, things are not that simple. To do this, you must use INSERT EXEC syntax to insert the output into a target table, and then get the value from the table and assign it to the variable, like this:
Copy Code code as follows:
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)
SELECT @RecordCount
DROP TABLE #T
the use of 2,sp_executesql
The sp_executesql command, introduced in SQL Server later than the EXEC command, provides better support for reusing execution plans.
To make a stark contrast to exec, let's see if we replace exec with sp_executesql in code 1 to see if we get the results we expect.
Copy Code code as follows:
DECLARE @TableName VARCHAR (m), @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) + ' ORDER by OrderID DESC '
EXEC sp_executesql @sql
Note the last line;
It turns out that it can be run;
sp_executesql provides interfaces
The sp_executesql command is more flexible than the EXEC command because it provides an interface that supports output parameters as well as an input parameter. This feature allows you to create query strings with parameters so that you can better reuse execution plans than exec, and sp_executesql's composition is very similar to stored procedures, except that you are dynamically building code. Its composition includes: code fast, parameter declaration part, parameter assignment part. If you say so much, just look at its grammar.
EXEC sp_executesql
@stmt = <statement>,--Similar stored procedure body
@params = <params>,--similar to the stored Procedure parameters section
<params assignment>--Similar to stored procedure calls
@stmt parameter is a dynamic batch of input that can introduce input or output parameters, just like the main 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 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 headers;
The @<params assignment> is similar to the exec part of the calling stored procedure.
To demonstrate that sp_executesql is better at managing the execution plan than exec, I'll use the code used earlier to discuss exec.
Copy Code code as follows:
DECLARE @TableName VARCHAR (m), @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
Empty the execution plan in the cache before calling the code and checking the execution plan it generates;
DBCC Freeproccache
The above dynamic code executes 3 times, each execution gives @orderid a different value, then queries the sys.syscacheobjects table and notices its output, the optimizer creates only one standby plan, and the plan is reused 3 times
Copy Code code as follows:
SELECT cacheobjtype,objtype,usecounts,sql from sys.syscacheobjects WHERE sql isn't 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;
Another powerful feature associated with Sq_executesql is that you can use output parameters to return a value to a variable in the calling 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 the OUTPUT clause when declaring the parameter. For example, the following static code simply demonstrates how to use an output parameter @p to return a value to a variable @i in an external batch from a dynamic batch.
Copy Code code as follows:
DECLARE @sql as NVARCHAR (), @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
The code returns output 10
The above is the main difference between exec and sp_executesql, if you reader think which is wrong or express not clear, also please point out ^_^
