sp_executesql is introduced in Microsoft® SQL Server™ version 7.0. Using sp_executesql is recommended over using the EXECUTE statement to execute a string. Not only does the support for parameter substitution make sp_executesql more versatile than EXECUTE, it also makes sp_executesql more efficient because it generates execution plans that are more likely to be reused by SQL Server.
When either sp_executesql or the EXECUTE statement executes a string, the string is executed as its own self-contained batch. SQL Server compiles the Transact-SQL statement or statements in the string into a execution plan that is separate from the execution plan of the batch that contained the sp_executesql or EXECUTE statement. These rules apply for these self-contained batches:
Executing these two batches illustrates these points:
/* 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
sp_executesql supports the substitution of parameter values for any parameters specified in the Transact-SQL string, but the EXECUTE statement does not. Therefore, the Transact-SQL strings generated by sp_executesql are more similar than those generated by the EXECUTE statement. The SQL Server query optimizer will probably match the Transact-SQL statements from sp_executesql with execution plans from the previously executed statements, saving the overhead of compiling a new execution plan.
With the EXECUTE statement, all parameter values must be converted to character or Unicode and made part of the 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 is executed repeatedly, a completely new Transact-SQL string must be built for each execution, even when the only differences are in the values supplied for the parameters. This generates extra overhead in several ways:
sp_executesql supports the setting of parameter values separately from the Transact-SQL string:
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 accomplishes the same task as the EXECUTE example shown earlier, but with these additional benefits:
Note Object names in the statement string must be fully qualified in order for SQL Server to reuse the execution plan.
In earlier versions of SQL Server, the only way to be able to reuse execution plans was to define the Transact-SQL statements as a stored procedure and have the application execute the stored procedure. This generates extra administrative overhead for the applications. Using sp_executesql can help reduce this overhead while still allowing SQL Server to reuse execution plans. sp_executesql can be used instead of stored procedures when executing a Transact-SQL statement a number of times, when the only variation is in the parameter values supplied to the Transact-SQL statement. Because the Transact-SQL statements themselves remain constant and only the parameter values change, the SQL Server query optimizer is likely to reuse the execution plan it generates for the first execution.
This example builds and executes a DBCC CHECKDB statement for every database on a 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(300)
FETCH NEXT FROM AllDatabases INTO @DBNameVar
WHILE (@@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 uses sp_executesql to implement SQLExecDirect when the Transact-SQL statement being executed contains bound parameter markers. The one exception is that sp_executesql is not used with data-at-execution parameters. This allows applications that use the standard ODBC functions, or that use the APIs defined over ODBC (such as RDO), to gain the advantages provided by sp_executesql. Existing ODBC applications ported to SQL Server 7.0 automatically acquire the performance gains without having to be rewritten. For more information, see Using Statement Parameters.
The Microsoft OLE DB Provider for SQL Server also uses sp_executesql to implement the direct execution of statements with bound parameters. Applications using OLE DB or ADO gain the advantages provided by sp_executesql without having to be rewritten.
| DECLARE @local_variable | SELECT |
| EXECUTE |