SQLSERVER dynamic execution of SQLsp_executesql and EXEC

We recommend that you use sp_executesql instead of EXECUTE statements to EXECUTE strings. Support Parameter replacement not only makes sp_executesql more common than EXECUTE, but also makes sp_executesql more effective because the execution plan it generates is more likely to be reused by SQLServer. Self-contained batch processing sp_executesql or EXECUTE

We recommend that you use sp_executesql instead of EXECUTE statements to EXECUTE strings. Support Parameter replacement not only makes sp_executesql more common than EXECUTE, but also makes sp_executesql more effective because the execution plan it generates is more likely to be reused by SQL Server. Self-contained batch processing sp_executesql or EXECUTE

RecommendedSp_executesqlInstead of using the EXECUTE statementRunString. Support Parameter replacement not onlySp_executesqlMore common than EXECUTE, and also makeSp_executesqlMore effective because it generatesRunThe plan is more likely to be reused by SQL Server.

Self-contained batch processing

Sp_executesqlOr EXECUTE statementRunThe string is processed as a self-contained batch.Run. SQL Server compiles statements in a Transact-SQL statement or string into oneRunPlan,RunPlan independent from includeSp_executesqlOr EXECUTE statement.RunPlan. The following rules apply to self-contained batch processing:

• UntilRun Sp_executesqlOr EXECUTE statementSp_executesqlOr compile the statements in the EXECUTE stringRunPlan.RunString.RunThe name referenced in the string is parsed.
  
  
• RunCan not be accessedSp_executesqlOr any variable declared in the batch where the EXECUTE statement is located. IncludeSp_executesqlOr the batchcompute statement cannot be accessed.RunVariable or local cursor defined in the string.
  
  
• IfRunIf the string has a USE statement for changing the database context, the change to the database context only persists.Sp_executesqlOr EXECUTE statement.

PassRunThe 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_executesql fails because @CharVariable has gone out of scope. */sp_executesql N'PRINT @CharVariable'GO/* Show database context resetting after sp_executesql completes. */USE pubsGOsp_executesql N'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 statementRunStatementRunPlan matching to save compilation of newRunPlanned overhead.

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 = 35SET @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 = 201SET @SQLString = N'SELECT * FROM pubs.dbo.employee WHERE job_lvl = ' +                 CAST(@IntVariable AS NVARCHAR(10))EXEC(@SQLString)

If the statement already existsRun, Even if the only difference is that the value provided by the parameter is differentRunYou must also generate a new Transact-SQL string. In this way, additional overhead is generated in the following aspects:

• The SQL Server query optimization tool combines the new Transact-SQL string with the existingRunPlan matching capability, which is hindered by variable parameter values in string text, especially in complex Transact-SQL statements.
  
  
• Each timeRunThe entire string must be regenerated.
  
  
• Each timeRunThe 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 = 35EXECUTE sp_executesql @SQLString, @ParmDefinition,                      @level = @IntVariable/* Execute the same string with the second parameter value. */SET @IntVariable = 32EXECUTE sp_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 A Transact-SQL statement is twiceRunSo the query optimizer should be ableRunInRunGeneratedRunPlan match. 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.

DescriptionTo make SQL Server re-useRunPlan, the object name in the statement string must fully comply with the requirements.

Reuse RunPlan

Use SQL Server in earlier versionsRunThe only way to plan is to define a Transact-SQL statement as a stored procedure and then make the applicationRunThis stored procedure. This generates additional overhead for managing applications. UseSp_executesqlHelps reduce this overhead and allow SQL Server to be reusedRunPlan. Multiple timesRunYou 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 statement for the first time.RunGeneratedRunPlan.

In the following example, each database except four system databases on the server is generated andRunDbcc checkdb statement:

USE masterGOSET NOCOUNT ONGODECLARE AllDatabases CURSOR FORSELECT name FROM sysdatabases WHERE dbid > 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')'   EXEC sp_executesql @Statement   PRINT CHAR(13) + CHAR(13)   FETCH NEXT FROM AllDatabases INTO @DBNameVarENDCLOSE AllDatabasesDEALLOCATE AllDatabasesGOSET NOCOUNT OFFGO

WhenRunThe SQL Server ODBC driver usesSp_executesqlCompleteSQLExecDirect. But the exception is:Sp_executesqlNot usedRun. This allows applications that use standard ODBC functions or APIs defined on ODBC (such as RDO) to useSp_executesqlBenefits. 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_executesqlDirectRunA statement with binding parameters. Applications using ole db or ADO can be obtained without rewriting.Sp_executesqlBenefits.

Original link:
Http://www.cnblogs.com/edobnet/archive/2004/11/10/62155.html