Since SQL Server Help Document is helpful to everyone's excellent query! It is recommended to use sp_executesql instead of using an Execute statement to perform a string. Support parameter replacement not only makes sp_executesql more common than Execute, but also makes sp_executesql more efficient because it generates the execution plan more likely to be reused by SQL Server.
Self-included batch
When the sp_executesql or execute statement executes a string, the string is executed as its own batch. SQL Server compiles the statement in the Transact-SQL statement or string into an execution plan, which is independent of the executive plan that contains the batch of sp_executesql or execute statement. The following rules apply to self-contained batch:
The Transact-SQL statement in sp_executesql or execute or Execute string is compiled into the execution plan until the sp_executesql or execute statement is executed. An error is started when you perform a string. The name referenced in the string is parsed when executed. The Transact-SQL statement in the execution string cannot access any variables declared in the batch of sp_executesql or execute statements. Batch containing sp_executesql or execute statements cannot access variables or partial cursors defined in the executed string. If you perform a string with a USE statement that changes the database context, the changes to the database context continue until the sp_executesql or the execute statement is completed.
Examples are illustrated by performing the following two batch processes:
/ * 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 the parameter
Sp_executeSQL supports replacement of any parameter values specified in the Transact-SQL string, but the execute statement does not support. Therefore, the Transact-SQL string generated by sp_executesql is more similar to the EXECUTE statement. The SQL Server Query Optimizer may match the Transact-SQL statement from sp_executesql with the execution plan of the previously performed statement to save the new implementation plan overhead.
When using the Execute statement, you must convert all parameter values to characters or Unicode and make it 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 repeated, even if only the difference is different for the value provided by the parameter, a new Transact-SQL string must also be generated each time it is executed. Thereby producing additional overhead in the following aspects:
The SQL Server Query Optimizer has the ability to match the new Transact-SQL string matching the existing implementation plan, which is constantly changing in the string text, especially in a complex Transact-SQL statement. Each string must be regenerated each time it is executed. The parameter value (not a character or unicode value) must be projected to the character or Unicode format each time execution.
Sp_executeSQL supports settings with the parameter value independent of 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 sample completion task is the same as the previous Execute example, but has the following additional advantages:
Because the actual text of the Transact-SQL statement does not change between execution, the query optimizer should match the transact-sql statement in the second execution with the execution plan generated during the first execution. This way, SQL Server does not have to compile the second statement. Transact-SQL strings only generate once. The integer parameters are specified in its own format. No need to convert to Unicode.
Description In order to reuse the execution plan, the object name in the statement string must be fully compliant. Re-use the execution plan
The only way to reuse the execution plan in the early versions of SQL Server is that the Transact-SQL statement is defined as the stored procedure and then make the application do this store. This produces additional overhead of managing applications. Use sp_executesql to help reduce this overhead and make SQL Server to reuse the execution plan. When a TRANSACT-SQL statement is to be executed multiple times, and the only change is to provide the parameter value for the Transact-SQL statement, SP_EXECUTESQL can be used instead of the stored procedure. Because the Transact-SQL statement itself keeps the same parameter value change, the SQL Server Query Optimizer may reuse the execution plan generated by the first execution.
The following example generates and executes DBCC Checkdb statement on each database other than 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
When the Transact-SQL statement currently executed contains the binding parameter tag, the SQL Server ODBC driver uses sp_executesql to complete SQLEXECDirect. However, the exception is that sp_executesql is not used in executing data parameters. This enables the use of standard ODBC functions or applications that use the APIs (such as RDO) defined on ODBC to utilize the advantages provided by sp_executesql. Existing ODBC applications located in SQL Server 2000 can automatically obtain performance gain without rewriting. For more information, see Using statement parameters.
The Microsoft OLE DB provider for SQL Server also uses sp_executesql to directly execute statements with binding parameters. The advantages provided by Sp_executeSQL can be obtained without having to override using OLE DB or ADO applications.
