The correct method for SQL server to execute dynamic SQL statements

How Should SQL server execute dynamic SQL statements? The following describes the two correct methods for SQL server to execute dynamic SQL statements. We hope that you will have a better understanding of SQL Server execution. dynamic SQL: codethatisexecuteddynamically. it is generally an SQL statement block dynamically combined based on user input or external conditions. dynamic SQL capability

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 capability

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 statement or batch process that can be repeatedly used or dynamically generated multiple times. transact-SQL statements or batch processing can contain embedded parameters. SP_EXECUTESQL and EXECUTE behave the same in batch processing, name scope, and database context. 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. While 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 WHEREGroupName = @ 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 WHEREGroupName = @ GroupName'

PRINT @ SQL;

EXEC SP_EXECUTESQL @ SQL, n' @ GroupNameNVARCHAR ', @ 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 variable defined in the batch processing. SP_EXECUTESQL can have input and output parameters, which is 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 use the @ GroupName value SuperAdmin, CommonUser, and CommonAdmin to EXECUTE the plan three times. Then, let's take a 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. % ';

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. % ';