Executing dynamic SQL statements in stored procedures

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 execution plans with sp_executesql, which provides execution performance and can 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

CodeDECLARE @TableName VARCHAR, @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 O ' Rderid 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 as 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;

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 it's not restricted.

The disadvantage of exec is that it does not provide an interface, where the interface means that it cannot execute a batch with a variable character, as follows

CodeDECLARE @TableName VARCHAR, @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 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 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 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, 10251,10252,10253. Then use the following code to query

Click F5 to run, we can see that every execution is generated once the compilation, 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

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:

Code

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 #T2

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.

Code

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 (@OrderID as VARCHAR) + ' ORDER by OrderID DESC '
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 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. Say so much, or look at its syntax:

  EXEC sp_executesql

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

   @params = <params>,--similar to the stored Procedure Parameters section, declaring parameter types

   <params assignment>--similar to a stored procedure call, to assign values to parameters, parameter values to be corresponding to the order of the parameters, you can also be assigned a value by specifying the parameter value for the parameter

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.

In fact @stmt, @params can be omitted, then exec sp_executesql syntax can be written in the following form:

  EXEC sp_executesql
   <statement>,
   <params>,
   <params 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.

Code

         DECLARE @TableName VARCHAR, @sql NVARCHAR (MAX), @OrderID INT;   
      SET @TableName = ' Orders ';
      SET @OrderID = 10251;   
      SET @sql = ' SELECT * from ' [e-mail protected] + ' WHERE OrderID = @OID ORDER by OrderID DESC '  
      Note that when you want to parameterize the table name of a dynamic SQL statement, you cannot:
      --set @sql = ' select * from @TableName where [e-mail protected] ORDER by Orderid Desc ',
     --If you are prompted to declare a scalar variable @tablename, you can only stitch the table name @tablename as the variable name as written above

      EXEC sp_executesql   
              @sql,   
             N ' @OID int ',   
            @OID = @OrderID

Let's look at the execution efficiency of exec sp_executesql, emptying the execution plan in the cache before invoking the code and checking the execution plan it generates;

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,sql from sys.syscacheobjects WHERE The SQL not is like '%cache% ' and SQL not like '%sys.% ' and SQL not like '%sp_executesql% '

Click F5 to run.

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 = 10 ';
  EXEC sp_executesql
         
        N ' @p as INT OUTPUT ',
        @p = @i OUTPUT
   SELECT @i  

Identifying Unicode string constants with the letter N prefix

summarize the following points:
I. Using EXCE sp_executesql efficiency is higher than exec, the same type of statement, just compile once, and exec executes several times need to compile several times.
Two. When constructing the WHERE clause of the dynamic SQL, which is the conditional clause, EXEC cannot use variables for positioning, the variables need to be converted to strings, and then spliced with dynamic SQL, which can cause SQL injection problems, as follows:

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

If you use the Exec sp_executesql, you can use the variable to do the positioning, and then give the parameter value of the release of dynamic SQL, the problem of avoiding SQL injection, as follows:

    SET @sql = ' SELECT * from ' [e-mail protected] + ' WHERE OrderID = @OID ORDER by OrderID DESC '  

Three. Whether exec or exec sp_executesql, if you want to dynamically parameterize table and column names, you cannot use table name parameters and column name parameters for positioning, and table name and column name parameters require the parameters of the stored procedure. For EXEC sp_ ExecuteSQL, the table name parameter and the column name parameter cannot be specified as a parameter declared in the exec sp_executesql parameter declaration section, such as:

Code

   Create PROCEDURE GetData
    @tbName nvarchar (10),
    @colName nvarchar (10),
    @Name nvarchar (10)
   As
    BEGIN
    declare @sql nvarchar (50);
    Set @sql = ' SELECT ' + @colName + ' from  ' [email protected]+ ' Where [email protected] ';
    -note that this sentence cannot be written as follows:
    --Set @sql = ' Select @colName from @tbName where [email protected] ';
    
     @sql,
     N ' @whereName nvarchar (10) ',
     @Name
    END

This means that the parameter declaration portion of the EXEC sp_executesql statement can only declare arguments to the WHERE clause of dynamic SQL.

Executing dynamic SQL statements in stored procedures