Dynamic SQL Execution parsing and passing parameters in SQL Server

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. (From msdn)

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 variables declared in batches that call 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.