Executes a Transact-SQL statement or batch that can be reused many times, or that has been built dynamically. The Transact-SQL statement or batch can contain embedded parameters.
sp_executesql [@stmt =] stmt
[
{, [@params =] N'@parameter_name data_type [,...n]' }
{, [@param1 =] 'value1' [,...n] }
]
stmt can contain parameters having the same form as a variable name, for example:
N'SELECT * FROM Employees WHERE EmployeeID = @IDParameter'
Each parameter included in stmt must have a corresponding entry in both the @params parameter definition list and the parameter values list.
0 (success) or 1 (failure)
Returns the result sets from all the SQL statements built into the SQL string.
sp_executesql has the same behavior as EXECUTE with regard to batches, the scope of names, and database context. The Transact-SQL statement or batch in the sp_executesql stmt parameter is not compiled until the sp_executesql statement is executed. The contents of stmt are then compiled and executed as an execution plan separate from the execution plan of the batch that called sp_executesql. The sp_executesql batch cannot reference variables declared in the batch calling sp_executesql. Local cursors or variables in the sp_executesql batch are not visible to the batch calling sp_executesql. Changes in database context last only to the end of the sp_executesql statement.
sp_executesql can be used instead of stored procedures to execute a Transact-SQL statement a number of times when the change in parameter values to the statement is the only variation. Because the Transact-SQL statement itself remains constant and only the parameter values change, the Microsoft® SQL Server™ query optimizer is likely to reuse the execution plan it generates for the first execution.
Note If object names in the statement string are not fully qualified, the execution plan is not reused.
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'
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
Being able to substitute parameters in sp_executesql offers these advantages to using the EXECUTE statement to execute a string:
Execute permissions default to the public role.
This example creates and executes a simple SELECT statement that contains an embedded parameter named @level.
execute sp_executesql
N'select * from pubs.dbo.employee where job_lvl = @level',
N'@level tinyint',
@level = 35
This example shows using sp_executesql to execute a dynamically built string. The example stored procedure is used to insert data into a set of tables used to partition sales data for a year. There is one table for each month of the year with the following format:
CREATE TABLE May1998Sales
(OrderID INT PRIMARY KEY,
CustomerID INT NOT NULL,
OrderDate DATETIME NULL
CHECK (DATEPART(yy, OrderDate) = 1998),
OrderMonth INT
CHECK (OrderMonth = 5),
DeliveryDate DATETIME NULL,
CHECK (DATEPART(mm, OrderDate) = OrderMonth)
)
For more information about retrieving data from these partitioned tables, see Using Views with Partitioned Data.
The name of each table consists of the first three letters of the month name, the four digits of the year, and the constant Sales. The name can be built dynamically from an order date:
/* Get the first three characters of the month name. */
SUBSTRING( DATENAME(mm, @PrmOrderDate), 1, 3) +
/* Concatenate the four-digit year; cast as character. */
CAST(DATEPART(yy, @PrmOrderDate) AS CHAR(4) ) +
/* Concatenate the constant 'Sales'. */
'Sales'
This sample stored procedure dynamically builds and executes an INSERT statement to insert new orders into the correct table. It uses the order date to build the name of the table that should contain the data, then incorporates that name into an INSERT statement. (This is a simple example for sp_executesql. It does not contain error checking and does not include checks for business rules, such as ensuring that order numbers are not duplicated between tables.)
CREATE PROCEDURE InsertSales @PrmOrderID INT, @PrmCustomerID INT,
@PrmOrderDate DATETIME, @PrmDeliveryDate DATETIME
AS
DECLARE @InsertString NVARCHAR(500)
DECLARE @OrderMonth INT
-- Build the INSERT statement.
SET @InsertString = 'INSERT INTO ' +
/* Build the name of the table. */
SUBSTRING( DATENAME(mm, @PrmOrderDate), 1, 3) +
CAST(DATEPART(yy, @PrmOrderDate) AS CHAR(4) ) +
'Sales' +
/* Build a VALUES clause. */
' VALUES (@InsOrderID, @InsCustID, @InsOrdDate,' +
' @InsOrdMonth, @InsDelDate)'
/* Set the value to use for the order month because
functions are not allowed in the sp_executesql parameter
list. */
SET @OrderMonth = DATEPART(mm, @PrmOrderDate)
EXEC sp_executesql @InsertString,
N'@InsOrderID INT, @InsCustID INT, @InsOrdDate DATETIME,
@InsOrdMonth INT, @InsDelDate DATETIME',
@PrmOrderID, @PrmCustomerID, @PrmOrderDate,
@OrderMonth, @PrmDeliveryDate
GO
Using sp_executesql in this procedure is more efficient than using EXECUTE to execute a string. When sp_executesql is used, there are only 12 versions of the INSERT string generated, 1 for each monthly table. With EXECUTE, each INSERT string is unique because the parameter values are different. Although both methods generate the same number of batches, the similarity of the INSERT strings generated by sp_executesql makes it more likely that the query optimizer will reuse execution plans.
| Batches | EXECUTE |
| Building Statements at Run Time | System Stored Procedures |