Statement generated at runtime-SQL

SQL Server 2008 books online (May 2009) Use sp_executesql

We recommend that you useSp_executesqlInstead of using the execute statement. Because this stored procedure supports parameter replacementSp_executesqlMore functions than execute, because SQL Server is more likely to be reusedSp_executesqlThe generated execution plan, soSp_executesqlIt is more effective than execute.

Self-contained batch processing

WhenSp_executesqlOr when the execute statement executes a string, the string is executed as its self-contained batch. SQL Server compiles one or more Transact-SQL statements in the string to be independent from batch processing (includingSp_executesqlOr execute statement) Execution Plan of the execution plan. The following rules apply to self-contained batch processing:

• In executionSp_executesqlOr before the execute statementSp_executesqlOr compile the statements in the execute string into the execution plan. The error is not analyzed or checked before the string is executed. The name referenced in the string is parsed only during execution.
• The Transact-SQL statement in the executed string cannot access the includeSp_executesqlOr any variable declared in the batchcompute statement. IncludeSp_executesqlOr the batch processing of the execute statement cannot access the variable or local cursor defined in the executed string.
• If the executed string contains a use statement to change the database context, the changes made to the database context will only lastSp_executesqlOr the execute statement ends.

Run the following two batches to illustrate these aspects:

Copy code

/*Show not having access to variables from the calling batch. */DECLARE @CharVariable CHAR(3);SET @CharVariable = 'abc';/* sp_executesql fails because @CharVariable has gone out of scope. */EXECUTE sp_executesql N'PRINT @CharVariable';GO/* Show database context resetting after sp_executesql finishes. */USE master;GOEXECUTE sp_executesql N'USE AdventureWorks;'GO/* This statement fails because the database context   has now returned to master. */SELECT * FROM Sales.Store;GO

Replace parameter values

Sp_executesqlYou can replace any parameter value specified in the transact-SQL string. However, the execute statement is not supported. ThereforeSp_executesqlThe generated Transact-SQL strings are more similar than those generated by the execute statement. The SQL Server Query Optimizer maySp_executesqlThe Transact-SQL statement of is matched with the execution plan of the previously executed statement, thus saving the overhead of compiling the new execution plan.

With the execute statement, all parameter values must be converted to characters or Unicode and be part of a Transact-SQL string.

If the statement is executed repeatedly, a new Transact-SQL string must be generated each time, even if only the provided parameter values are different. This will generate additional overhead in the following way:

• Changing the parameter values in the string text (especially complex Transact-SQL statements) will affect the SQL Server Query Optimizer's function of matching the new Transact-SQL string with the existing execution plan.
• The entire string must be regenerated during each execution.
• Each execution must convert the parameter value (non-character or Unicode value) to character or unicode format.

Sp_executesqlYou can use a Transact-SQL string to set the parameter values:

Copy code

DECLARE @IntVariable INT;DECLARE @SQLString NVARCHAR(500);DECLARE @ParmDefinition NVARCHAR(500);/* Build the SQL string one time. */SET @SQLString =     N'SELECT * FROM AdventureWorks.Sales.Store WHERE SalesPersonID = @SalesID';/* Specify the parameter format one time. */SET @ParmDefinition = N'@SalesID int';/* Execute the string with the first parameter value. */SET @IntVariable = 275;EXECUTE sp_executesql @SQLString, @ParmDefinition,                      @SalesID = @IntVariable;/* Execute the same string with the second parameter value. */SET @IntVariable = 276;EXECUTE sp_executesql @SQLString, @ParmDefinition,                      @SalesID = @IntVariable;

Sp_executesqlIt also has the following advantages:

• Because the actual text of the transact-SQL statement is not changed between two executions, therefore, the query optimizer can match the statements in the second execution with the execution plan generated during the first execution. Therefore, 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.

  Note:
  The object name in the statement string must comply with the SQL Server reuse execution plan requirements.

Reuse execution plan

In earlier versions of SQL Server, the only way to reuse the execution plan is to define the transact-SQL statement as a stored procedure and let the application execute the stored procedure. This generates additional overhead for managing applications. UseSp_executesqlThis can help reduce this overhead and allow SQL Server to reuse execution plans. When the parameter value provided to a Transact-SQL statement has only one variable, you can useSp_executesqlInstead of using other stored procedures. Because the transact-SQL statement remains unchanged and only the parameter value is changed, the SQL Server Query Optimizer may reuse the execution plan generated during the first execution.

The following example generates and executes each database on the server (except the four system databases ).DBCC CHECKDBStatement.

Copy code

USE master;GOSET NOCOUNT ON;GODECLARE AllDatabases CURSOR FORSELECT name FROM sys.databases WHERE database_id > 4OPEN 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')' + N'WITH PHYSICAL_ONLY'   EXEC sp_executesql @Statement   PRINT CHAR(13) + CHAR(13)   FETCH NEXT FROM AllDatabases INTO @DBNameVarENDCLOSE AllDatabasesDEALLOCATE AllDatabasesGOSET NOCOUNT OFF;GO

The SQL Server ODBC driver usesSp_executesqlTo implementSqlexecdirect. In this way, you canSp_executesqlThe benefits are extended to all applications that use ODBC or APIs defined through ODBC (such as rdo. Existing ODBC applications connected to SQL Server can automatically improve performance without rewriting. However, there is one exception. If it is an execution data parameter, it cannot be usedSp_executesql. For more information, see use statement parameters.

SQL Server native client ODBC access interface is also usedSp_executesqlTo directly execute the statement with the bound parameters. Applications using ole db or ADO can be used without rewritingSp_executesqlBenefits.

See

Other resources

SQL Injection
Declare @ local_variable (TRANSACT-SQL)
Select (TRANSACT-SQL)
Sp_executesql (TRANSACT-SQL)

Help and information

Obtain SQL Server 2008 Help