Dynamic execution of SQL statements in SQL Server (from: http://hi.baidu.com/senty/blog/item/5d6d36d3dc89a5073bf3cf3e.html)

RecommendedSp_executesqlInstead of using the execute statement to execute a string. Support Parameter replacement not onlySp_executesql More common than execute, and also makeSp_executesqlIt is more effective because the execution plan generated by SQL Server is more likely to be reused by SQL Server.

Self-contained batch processing

Sp_executesqlOr when the execute statement executes a string, the string is executed as its self-contained batch. SQL Server compiles statements in a Transact-SQL statement or string into an execution plan, which is independentSp_executesqlOr execute statement. The following rules apply to self-contained batch processing:

• Until executionSp_executesqlOr execute statementSp_executesql Or compile the statements in the execute string into the execution plan. When the string is executed, the analysis or check for its errors starts. The name referenced in the string is parsed only during execution.
• The Transact-SQL statement in the executed string cannot be accessed.Sp_executesqlOr any variable declared in the batch where the execute statement is located. IncludeSp_executesqlOr the batch processing of the execute statement cannot access the variable or local cursor defined in the executed string.
• If the execution string has the use statement for changing the database context, the changes to the database context only continueSp_executesqlOr execute statement.

The following two batches are used as examples:

/* Show not having access to variables from the calling batch. */declare @ charvariable char (3) set @ charvariable = 'abc '/*Sp_executesqlFails because @ charvariable has gone out of scope .*/Sp_executesqlN'print @ charvariable' go/* Show database context resetting afterSp_executesqlCompletes. */use pubsgoSp_executesqlN 'use northwind 'Go/* This statement fails because the database context has now returned to pubs. */select * From shippersgo
 

Replace parameter values

Sp_executesqlYou can replace the parameter values of any parameter specified in the transact-SQL string. However, the execute statement does not. ThereforeSp_executesqlThe generated Transact-SQL string is more similar than that generated by the execute statement. The SQL Server Query Optimizer maySp_executesqlThe Transact-SQL statement of matches the execution plan of the previously executed statement to save the overhead of compiling the new execution plan.

When using an execute statement, you must convert all parameter values to characters or Unicode and make them part of a Transact-SQL string:

Declare @ intvariable intdeclare @ sqlstring nvarchar (500)/* build and execute a string with one parameter value. */set @ intvariable = 35 set @ sqlstring = n' select * from pubs. DBO. employee where job_lvl = '+ Cast (@ intvariable as nvarchar (10) exec (@ sqlstring)/* build and execute a string with a second parameter value. */set @ intvariable = 201 set @ sqlstring = n' select * from pubs. DBO. employee where job_lvl = '+ Cast (@ intvariable as nvarchar (10) exec (@ sqlstring)
 

If the statement is executed repeatedly, a new Transact-SQL string must be generated during each execution even if the value provided by the parameter is different. In this way, additional overhead is generated in the following aspects:

• The SQL Server query optimizer can match the new Transact-SQL string with the existing execution plan, which is hindered by the changing parameter values in the string text, especially in complex Transact-SQL statements.
• The entire string must be regenerated during each execution.
• During each execution, the parameter value (not a character or Unicode value) must be projected into character or unicode format.

Sp_executesqlYou can set parameter values that are independent of the transact-SQL string:

Declare @ intvariable intdeclare @ sqlstring nvarchar (500) Declare @ parmdefinition nvarchar (500)/* build the SQL string once. */set @ sqlstring = n' select * from pubs. DBO. employee where job_lvl = @ level '/* specify the parameter format once. */set @ parmdefinition = n' @ level tinyint '/* execute the string with the first parameter value. */set @ intvariable = 35 ExecuteSp_executesql@ Sqlstring, @ parmdefinition, @ level = @ intvariable/* execute the same string with the second parameter value. */set @ intvariable = 32 ExecuteSp_executesql@ Sqlstring, @ parmdefinition, @ level = @ intvariable
 

ThisSp_executesqlThe tasks completed in the example are the same as those completed in the previous execute example, but they have the following additional advantages:

• Because the actual text of the transact-SQL statement has not changed between two executions, therefore, the query optimizer should be able to match the transact-SQL statement in the second execution with the execution plan generated during the first execution. In this way, SQL server does not have to compile the second statement.
• A Transact-SQL string is generated only once.
• The integer parameter is specified in its own format. Conversion to Unicode is not required.

 

DescriptionIn order for SQL Server to re-use the execution plan, the object name in the statement string must fully comply with the requirements.

 

Reuse execution plan

In earlier versions of SQL Server, the only way to re-use the execution plan is to define the transact-SQL statement as a stored procedure and then make the applicationProgramExecute this stored procedure. This generates additional overhead for managing applications. UseSp_executesqlThis helps reduce this overhead and allow SQL Server to reuse the execution plan. You can useSp_executesqlTo replace 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 the following example, the DBCC checkdb statement is generated and executed for each database except the four system databases on the server:

  Use mastergoset nocount ongodeclare alldatabases cursor forselect name from sysdatabases where dbid> 4 open alldatabasesdeclare @ dbnamevar nvarchar (128) Declare @ statement nvarchar (300) fetch next from alldatabases into @ dbnamevarwhile (@ fetch_status = 0) Begin print n 'checking database' + @ dbnamevar set @ Statement = n 'use' + @ dbnamevar + char (13) + n'dbcc checkdb ('+ @ dbnamevar + N')'   exec  sp_executesql  @ statement   Print char (13) + char (13)   fetch next from alldatabases into @ dbnamevar   end   close alldatabases   deallocate alldatabases   go   set nocount off   go  

The SQL Server ODBC driver usesSp_executesqlCompleteSqlexecdirect. But the exception is:Sp_executesqlIt is not used for data parameters in execution. This allows applications that use standard ODBC functions or APIs defined on ODBC (such as rdo) to useSp_executesql Benefits. The existing ODBC applications located in SQL Server 2000 can automatically obtain performance gains without rewriting. For more information, see use statement parameters.

The Microsoft ole db provider for SQL Server also usesSp_executesqlDirectly execute the statement with the binding parameter. Applications using ole db or ADO can be obtained without rewriting.Sp_executesqlBenefits.