Export (0) Print
Expand All
Expand Minimize

sp_executesql

SQL Server 2000

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.

Syntax

sp_executesql [@stmt =] stmt
[
    
{, [@params =] N'@parameter_name  data_type [,...n]' }
    {, [@param1 =] 'value1' [,...n] }
]

Arguments

[@stmt =] stmt

Is a Unicode string containing a Transact-SQL statement or batch. stmt must be either a Unicode constant or a variable that can be implicitly converted to ntext. More complex Unicode expressions (such as concatenating two strings with the + operator) are not allowed. Character constants are not allowed. If a constant is specified, it must be prefixed with an N. For example, the Unicode constant N'sp_who' is legal, but the character constant 'sp_who' is not. The size of the string is limited only by available database server memory.

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.

[@params =] N'@parameter_name  data_type [,...n]'

Is one string that contains the definitions of all parameters that have been embedded in stmt. The string must be either a Unicode constant or a variable that can be implicitly converted to ntext. Each parameter definition consists of a parameter name and a data type. n is a placeholder indicating additional parameter definitions. Every parameter specified in stmt must be defined in @params. If the Transact-SQL statement or batch in stmt does not contain parameters, @params is not needed. The default value for this parameter is NULL.

[@param1 =] 'value1'

Is a value for the first parameter defined in the parameter string. The value can be a constant or a variable. There must be a parameter value supplied for every parameter included in stmt. The values are not needed if the Transact-SQL statement or batch in stmt has no parameters.

n

Is a placeholder for the values of additional parameters. Values can be only constants or variables. Values cannot be more complex expressions such as functions, or expressions built using operators.

Return Code Values

0 (success) or 1 (failure)

Result Sets

Returns the result sets from all the SQL statements built into the SQL string.

Remarks

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:

  • Because the actual text of the Transact-SQL statement in the sp_executesql string does not change between executions, the query optimizer will probably match the Transact-SQL statement in the second execution with the execution plan generated for the first execution. Therefore, SQL Server does not have to compile the second statement.

  • The Transact-SQL string is built only once.

  • The integer parameter is specified in its native format. Casting to Unicode is not required.
Permissions

Execute permissions default to the public role.

Examples
A. Execute a simple SELECT statement

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
B. Execute a dynamically built string

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.

See Also

Batches

EXECUTE

Building Statements at Run Time

System Stored Procedures

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft