Use of sp_executesql stored procedures in SQL help _mssql

Excerpt from the SQL Server Help document
For everyone excellent query speed help!

It is recommended that you use sp_executesql instead of executing the string using the EXECUTE statement. Supporting parameter substitution not only makes sp_executesql more generic than EXECUTE, but also makes sp_executesql more efficient because it generates execution plans that are more likely to be reused by SQL Server.

Self-contained batch processing

When a sp_executesql or EXECUTE statement executes a string, the string is executed as a self-contained batch. SQL Server compiles statements in Transact-SQL statements or strings into an execution plan that is independent of the execution plan of the batch that contains the sp_executesql or EXECUTE statements. The following rules apply to self contained batches:

• Transact-SQL statements in sp_executesql or execute strings are compiled into the execution plan until the sp_executesql or EXECUTE statement is executed. parsing or checking for errors is not started until the string is executed. The names referenced in the string are parsed when executed.
• A Transact-SQL statement in the executed string that cannot access any of the variables declared in the batch where the sp_executesql or EXECUTE statement resides. A batch containing a sp_executesql or EXECUTE statement cannot access a variable or local cursor defined in the executed string.
• If the execution string has a use statement that changes the database context, the changes to the database context persist only until the sp_executesql or EXECUTE statement completes.

Examples are provided by performing the following two batches:

/* 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 pubs
GO
sp_executesql N'USE Northwind'
GO
/* This statement fails because the database context
  has now returned to pubs. */
SELECT * FROM Shippers
GO

Replace parameter value

sp_executesql supports the substitution of parameter values for any parameters specified in Transact-SQL strings, but is not supported by the EXECUTE statement. As a result, Transact-SQL strings generated by sp_executesql are more similar than those generated by the EXECUTE statement. The SQL Server query optimizer may save the overhead of compiling a new execution plan by matching Transact-SQL statements from sp_executesql with the execution plans of previously executed statements.

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

DECLARE @IntVariable INT
DECLARE @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 repeats, even if the only difference is that the value provided by the parameter is different, a new Transact-SQL string must be generated for each execution. This creates additional overhead in the following areas:

• The SQL Server query optimizer has the ability to match a new Transact-SQL string to an existing execution plan, which is hampered by parameter values that are constantly changing in string literals, especially in complex Transact-SQL statements.
• The entire string must be regenerated each time it is executed.
• parameter values (not characters or Unicode values) must be projected on each execution to a character or Unicode format.

sp_executesql supports the setting of parameter values independent of Transact-SQL strings:

DECLARE @IntVariable INT
DECLARE @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
EXECUTE sp_executesql @SQLString, @ParmDefinition,
           @level = @IntVariable
/* Execute the same string with the second parameter value. */
SET @IntVariable = 32
EXECUTE sp_executesql @SQLString, @ParmDefinition,
           @level = @IntVariable

This sp_executesql example completes the same task as the previous EXECUTE example, but has the following additional benefits:

• Because the actual text of a Transact-SQL statement does not change between two executions, the query optimizer should be able to match Transact-SQL statements in the second execution with the execution plan that was generated the first time it was executed. As a rule, SQL Server does not have to compile the second statement.
• Transact-SQL strings are generated only once.
• Integer parameters are specified in their own format. You do not need to convert to Unicode.

description to enable SQL Server to reuse the execution plan, the object names in the statement string must fully conform to the requirements.

Re-use the execution plan

The only way to reuse an execution plan in an earlier version of SQL Server is to define a Transact-SQL statement as a stored procedure and then let the application execute the stored procedure. This creates additional overhead for managing applications. Using sp_executesql helps reduce this overhead and enables SQL Server to reuse the execution plan. You can use sp_executesql instead of a stored procedure when you want to execute a Transact-SQL statement more than once, and the only change is the parameter value provided to the Transact-SQL statement. Because the Transact-SQL statement itself remains unchanged, only the parameter values change, the SQL Server query optimizer may reuse the execution plan that was generated on first execution.

The following example generates and executes a DBCC CHECKDB statement for each database on the server except for the four system databases:

  use master go SET NOCOUNT on Go DECLARE alldatabases CURSOR for SELECT name from sysdatabases WHERE dbid > 4 OPEN alldatabases DECLARE @DBNameVar NVARCHAR (128) DECLARE @Statement NVARCHAR () FETCH NEXT from alldatabases into @DBNameVar while (@ @FETCH_STATUS = 0) BEGIN PRINT N ' CHECKING DATABASE ' + @DBNameVar SET @Statement = n ' use ' + @DBNam EVar + CHAR + n ' DBCC CHECKDB (' + @DBNameVar + n ') '   EXEC sp_executesql @Statement   PRI NT CHAR + char   FETCH NEXT from alldatabases to @DBNameVar   end   Close Al Ldatabases   deallocate alldatabases   go   SET NOCOUNT out   go  
   

The SQL Server ODBC driver uses sp_executesql to complete the SQLExecDirectwhen a Transact-SQL statement currently executing contains a binding parameter tag. The exception, however, is that sp_executesql is not used for data parameters in execution. This makes it possible for applications that use standard ODBC functions or APIs defined on ODBC, such as RDO, to take advantage of the advantages offered by sp_executesql . An existing ODBC application that is located in SQL Server 2000 does not need to be rewritten to automatically gain performance gains. For more information, see Using statement parameters.

The Microsoft OLE DB provider for SQL Server also uses sp_executesql to execute statements with binding parameters directly. An application that uses OLE DB or ADO can gain the benefits provided by sp_executesql without rewriting it.

1. Execute combined SQL with output parameters

declare @Dsql nvarchar (1000),
@Name varchar (50),
@TablePrimary varchar (50),
@TableName varchar (50),
@ASC int

Set @TablePrimary = ' ID ';
Set @TableName = ' fine ';
Set @ASC = 1;

Set @Dsql =n ' Select @Name = ' + @TablePrimary +n ' from ' + @TableName +n ' ORDER BY ' + @TablePrimary + (case @ASC when ' 1 ' then N ' DESC ' ELSE N ' ASC ' end)
Print @Dsql

Set ROWCOUNT 7
EXEC sp_executesql @Dsql, N ' @Name varchar output ', @Name output
Print @Name
Set ROWCOUNT 0

2. Execute combined SQL with input parameters

  DECLARE @IntVariable INT DECLARE @SQLString NVARCHAR () DECLARE @ParmDefinition NVARCHAR (+) * Build the S QL 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 ' parameter value '. */SET @IntVariable = Execute sp_executesql @SQLString, @ParmDefinition, @level = @IntVariable/* Execute th E same string with the second parameter value. */SET @IntVariable = EXECUTE sp_executesql @SQLString, @ParmDefinition, @level = @IntVariable