These two methods allow SQL Server to correctly execute dynamic SQL

What should I do if SQL Server executes dynamic SQL statements? The following describes the two correct ways for SQL Server to execute dynamic SQL. We hope that you can have a better understanding of SQL Server to execute dynamic SQL.

Dynamic SQL: code that is executed dynamically. It is generally an SQL statement block dynamically combined based on user input or external conditions. Dynamic SQL can give full play to the powerful functions of SQL and easily solve problems that are difficult to solve by other methods. I believe that anyone who has used dynamic SQL can realize the convenience it brings. However, dynamic SQL is sometimes not as good as static SQL in execution performance (efficiency) and is not suitable for use, there are often security risks (SQL injection attacks ).

Dynamic SQL can be executed through EXECUTE or SP_EXECUTESQL.

EXECUTE

Execute a command string or string in a Transact-SQL batch or execute one of the following modules: system stored procedures, user-defined stored procedures, scalar value user-defined functions, or extended stored procedures. SQL Server 2005 extends the EXECUTE statement to send commands to the linked Server. In addition, you can explicitly set the context for executing strings or commands.

SP_EXECUTESQL

Execute a Transact-SQL statement or batch process that can be repeatedly used or dynamically generated multiple times. Transact-SQL statements or batch processing can contain embedded parameters. In terms of batch processing, name scope, and database context, SP_EXECUTESQL performs the same behavior as EXECUTE. The Transact-SQL statement or batch processing in the SP_EXECUTESQL stmt parameter is compiled only when the SP_EXECUTESQL statement is executed. Then, the content in stmt will be compiled and run as an execution plan. This execution plan is independent of the Execution Plan for batch processing called SP_EXECUTESQL. SP_EXECUTESQL batch processing cannot reference the variable declared in the batch processing that calls SP_EXECUTESQL. The local cursor or variable in SP_EXECUTESQL batch processing is invisible to the batch processing that calls SP_EXECUTESQL. Changes made to the database context are valid only before the end of the SP_EXECUTESQL statement.

If only the parameter values in the statement are modified, sp_executesql can be used to execute the Transact-SQL statement multiple times instead of the stored procedure. Because the Transact-SQL statement remains unchanged and only the parameter value changes, the SQL Server Query Optimizer may reuse the execution plan generated during the first execution.

In general, we recommend that you use SP_EXECUTESQL to execute dynamic SQL statements. On the one hand, it is more flexible and can have input and output parameters. On the other hand, the query optimizer is more likely to reuse the execution plan, improve Execution efficiency. In addition, the use of SP_EXECUTESQL can improve security. Of course, it does not mean that EXECUTE should be abandoned completely. In specific cases, EXECUTE is more convenient than SP_EXECUTESQL. For example, dynamic SQL strings are of the VARCHAR type and are not of the NVARCHAR type. SP_EXECUTESQL can only EXECUTE Unicode strings or constants or variables that can be implicitly converted to ntext, while EXECUTE can EXECUTE both types of strings.

Next we will compare some details of EXECUTE and SP_EXECUTESQL.

 
 

  
  
EXECUTE (n' SELECT * FROM Groups ') -- execution successful
  
  
EXECUTE ('select * FROM groups') -- execution successful
  
  
SP_EXECUTESQL n' SELECT * FROM Groups '; -- execution successful
  
  
SP_EXECUTESQL 'select * FROM Groups '-- execution error
 
 

Summary: EXECUTE can EXECUTE non-Unicode or Unicode string constants and variables. SP_EXECUTESQL can only execute Unicode or string constants and variables that can be implicitly converted to ntext.

 
 

  
  
DECLARE @ GroupName VARCHAR (50); SET @ GroupName = 'superadmin ';
  
  
EXECUTE ('select * FROM Groups WHERE GroupName = ''' + SUBSTRING (@ GroupName,) + '''); -- there is a syntax error near 'string.
  
  
DECLARE @ SQL VARCHAR (200 );
  
  
DECLARE @ GroupName VARCHAR (50); SET @ GroupName = 'superadmin ';
  
  
SET @ SQL = 'select * FROM Groups WHERE GroupName = ''' + SUBSTRING (@ GroupName, 1, 5) + ''''
  
  
-- PRINT @ SQL; EXECUTE (@ SQL );
 
 

Summary: EXECUTE brackets can only contain string variables, string constants, or their connection combinations. Other functions and stored procedures cannot be called. If you want to use it, use a combination of variables, as shown in the preceding figure.

 
 

  
  
DECLARE @ SQL VARCHAR (200 );
  
  
DECLARE @ GroupName VARCHAR (50); SET @ GroupName = 'superadmin ';
  
  
SET @ SQL = 'select * FROM Groups WHERE GroupName = @ GroupName'
  
  
-- PRINT @ SQL; EXECUTE (@ SQL); -- error: the scalar variable "@ GroupName" must be declared ". SET @ SQL = 'select * FROM Groups WHERE GroupName = '+ QUOTENAME (@ GroupName ,'''')
  
  
EXECUTE (@ SQL); -- correct:
  
  
DECLARE @ SQL NVARCHAR (200 );
  
  
DECLARE @ GroupName NVARCHAR (50); SET @ GroupName = 'superadmin ';
  
  
SET @ SQL = 'select * FROM Groups WHERE GroupName = @ GroupName'
  
  
PRINT @ SQL;
  
  
EXEC SP_EXECUTESQL @ SQL, n' @ GroupName NVARCHAR ', @ GroupName
 
 

No results are found, and no parameter length is declared.

 
 

  
  
DECLARE @Sql NVARCHAR(200);  
  
  
DECLARE @GroupName NVARCHAR(50);SET@GroupName ='SuperAdmin';  
  
  
SET@Sql ='SELECT * FROM Groups WHERE GroupName=@GroupName' 
  
  
PRINT @Sql;  
  
  
EXEC SP_EXECUTESQL @Sql, N'@GroupName NVARCHAR(50)',@GroupName  
 
 

Summary: Dynamic batch processing cannot access the local variables defined in the batch processing. SP_EXECUTESQL can have input and output parameters, which are more flexible than EXECUTE.

Next, let's take a look at the execution efficiency of EXECUTE and SP_EXECUTESQL. First, clear the cache execution plan, and then EXECUTE it three times with the @ GroupName value SuperAdmin, CommonUser, and CommonAdmin. Then let's look at the cached information.

 
 

  
  
DBCC FREEPROCCACHE;  
  
  
DECLARE @Sql VARCHAR(200);  
  
  
DECLARE @GroupName VARCHAR(50);SET@GroupName ='SuperAdmin'; --'CommonUser', 'CommonAdmin'  
  
  
SET@Sql ='SELECT * FROM Groups WHERE GroupName=' + QUOTENAME(@GroupName, '''')  
  
  
EXECUTE(@Sql); SELECTcacheobjtype, objtype, usecounts, sql  
  
  
FROM sys.syscacheobjects  
  
  
WHERE sql NOTLIKE '%cache%' 
  
  
ANDsql NOTLIKE '%sys.%';  
 
 

As shown in:

Let's look at the execution efficiency of SP_EXECUTESQL.

 
 

  
  
DBCC FREEPROCCACHE;  
  
  
DECLARE @Sql NVARCHAR(200);  
  
  
DECLARE @GroupName NVARCHAR(50);SET@GroupName ='SuperAdmin'; --'CommonUser', 'CommonAdmin'  
  
  
SET@Sql ='SELECT * FROM Groups WHERE GroupName=@GroupName' 
  
  
EXECUTESP_EXECUTESQL @Sql, N'@GroupName NVARCHAR(50)', @GroupName;  
  
  
SELECTcacheobjtype, objtype, usecounts, sql  
  
  
FROM sys.syscacheobjects  
  
  
WHERE sql NOTLIKE '%cache%' 
  
  
ANDsql NOTLIKE '%sys.%';  
 
 

Summary: EXEC generates three independent ad hoc execution plans, while SP_EXECUTESQL is used to generate only one execution plan, which is reused three times, there are many such dynamic SQL statements that are frequently executed. Using SP_EXECUTESQL can improve the performance.