sp_executesql (Transact-SQL)

Actualizado: 5 de diciembre de 2005

Ejecuta una instrucción o lote Transact-SQL que puede volver a utilizarse muchas veces o uno que se ha generado de forma dinámica. La instrucción o el lote Transact-SQL puede contener parámetros incrustados.

Convenciones de sintaxis de Transact-SQL

Sintaxis

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

Argumentos

• [ @stmt = ] stmt
  Es una cadena Unicode que contiene un lote o una instrucción de Transact-SQL. stmt debe ser una constante Unicode o una variable Unicode. No se permite utilizar expresiones Unicode más complejas, como una concatenación de dos cadenas con el operador +. Las constantes de caracteres no están permitidas. Si se especifica una constante Unicode, debe llevar el prefijo N. Por ejemplo, la constante Unicode N'sp_who' es válida, pero la constante de caracteres 'sp_who' no lo es. El tamaño de la cadena sólo está limitado por la memoria disponible en el servidor de bases de datos. En los servidores de 64 bits, el tamaño de la cadena está limitado a 2 GB, el tamaño máximo de nvarchar(max).

  [!NOTA] stmt puede contener parámetros que tengan el mismo formato que un nombre de variable, por ejemplo: N'SELECT * FROM HumanResources.Employee WHERE EmployeeID = @IDParameter'

  Cada parámetro incluido en stmt debe tener una entrada correspondiente en la lista de definición de parámetros @params y en la lista de valores de parámetros.

• [ @params = ] **N'@**parameter_namedata_type[ ,... n ] '
  Es una cadena que contiene las definiciones de todos los parámetros que se han incrustado en stmt. La cadena debe ser una constante Unicode o una variable Unicode. Cada definición de parámetro se compone de un nombre de parámetro y un tipo de datos. n es un marcador de posición que indica definiciones de parámetros adicionales. Todos los parámetros especificados en stmt deben definirse en @params. Si el lote o la instrucción de Transact-SQL de stmt no contiene parámetros, @params no es necesario. El valor predeterminado de este parámetro es NULL.

• [ **@**param1 = ] 'value1'
  Es un valor para el primer parámetro definido en la cadena de parámetros. El valor puede ser una constante Unicode o una variable Unicode. Debe proporcionarse un valor de parámetro para cada parámetro incluido en stmt. Los valores no son necesarios cuando el lote o la instrucción de Transact-SQL de stmt no tiene parámetros.

• [ OUT | OUTPUT ]
  Indica que se trata de un parámetro de salida. Los parámetros text, ntext e image se pueden utilizar como parámetros de OUTPUT, a menos que el procedimiento sea un procedimiento Common Language Runtime (CLR). Un parámetro de salida que utilice la palabra clave OUTPUT puede ser un marcador de posición de cursor, a menos que el procedimiento sea un procedimiento CLR.

• n
  Es un marcador de posición para los valores de los parámetros adicionales. Los valores sólo pueden ser constantes o variables. Los valores no pueden ser expresiones más complejas como funciones ni expresiones generadas mediante operadores.

Conjuntos de resultados

Devuelve los conjuntos de resultados de todas las instrucciones SQL integradas en la cadena SQL.

Notas

sp_executesql tiene el mismo comportamiento que EXECUTE en cuanto a los lotes, el ámbito de los nombres y el contexto de las bases de datos. El lote o la instrucción de Transact-SQL del parámetro stmt de sp_executesql no se compila hasta que se ejecuta la instrucción sp_executesql. Entonces, el contenido de stmt se compila y se ejecuta como un plan de ejecución independiente del que corresponde al lote que ha llamado a sp_executesql. El lote de sp_executesql no puede hacer referencia a las variables declaradas en el lote que llama a sp_executesql. Los cursores o las variables locales del lote de sp_executesql no son visibles para el lote que llama a sp_executesql. Los cambios en el contexto de base de datos sólo se mantienen hasta el final de la instrucción sp_executesql.

sp_executesql puede utilizarse como alternativa a los procedimientos almacenados para ejecutar varias veces una instrucción Transact-SQL si la única variación es que cambian los valores de los parámetros de la instrucción. Al permanecer constante la propia instrucción Transact-SQL y variar sólo los valores de los parámetros, es probable que el optimizador de consultas de SQL Server vuelva a utilizar el plan de ejecución que genera para la primera ejecución.

[!NOTA] Para mejorar el rendimiento, utilice nombres de objeto completos en la cadena de la instrucción.

sp_executesql permite establecer los valores de los parámetros independientemente de la cadena Transact-SQL, como se muestra en el siguiente ejemplo.

DECLARE @IntVariable int;
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);

/* Build the SQL string one time.*/
SET @SQLString =
     N'SELECT EmployeeID, NationalIDNumber, Title, ManagerID
       FROM AdventureWorks.HumanResources.Employee 
       WHERE ManagerID = @ManagerID';
SET @ParmDefinition = N'@ManagerID tinyint';
/* Execute the string with the first parameter value. */
SET @IntVariable = 197;
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @ManagerID = @IntVariable;
/* Execute the same string with the second parameter value. */
SET @IntVariable = 109;
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @ManagerID = @IntVariable;

Los parámetros de salida también se pueden utilizar con sp_executesql. En el ejemplo siguiente se recupera un puesto de la tabla AdventureWorks.HumanResources.Employee y se devuelve en el parámetro de salida @max_title.

DECLARE @IntVariable int;
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);
DECLARE @max_title varchar(30);

SET @IntVariable = 197;
SET @SQLString = N'SELECT @max_titleOUT = max(Title) 
   FROM AdventureWorks.HumanResources.Employee
   WHERE ManagerID = @level';
SET @ParmDefinition = N'@level tinyint, @max_titleOUT varchar(30) OUTPUT';

EXECUTE sp_executesql @SQLString, @ParmDefinition, @level = @IntVariable, @max_titleOUT=@max_title OUTPUT;
SELECT @max_title;

La posibilidad de sustituir los parámetros de sp_executesql ofrece las siguientes ventajas con respecto al uso de la instrucción EXECUTE para ejecutar una cadena:

• Debido a que el texto real de la instrucción Transact-SQL de la cadena de sp_executesql no cambia entre ejecuciones, es posible que el optimizador de consultas utilice la instrucción Transact-SQL de la segunda ejecución con el plan de ejecución generado en la primera. De este modo, SQL Server no tiene que compilar la segunda instrucción.
• La cadena de Transact-SQL sólo se genera una vez.
• El parámetro de tipo integer se especifica en su formato nativo. No es necesaria la conversión a Unicode.

Permisos

Debe pertenecer a la función public.

Valores de código de retorno

0 (correcto) o distinto de cero (error)

Ejemplos

A. Ejecutar una instrucción SELECT simple

En el siguiente ejemplo se crea y se ejecuta una instrucción SELECT simple que contiene un parámetro incrustado denominado @level.

EXECUTE sp_executesql 
          N'SELECT * FROM AdventureWorks.HumanResources.Employee 
          WHERE ManagerID = @level',
          N'@level tinyint',
          @level = 109;

B. Ejecutar una cadena generada de forma dinámica

En el siguiente ejemplo se muestra el uso de sp_executesql para ejecutar una cadena generada de forma dinámica. El procedimiento almacenado de ejemplo se utiliza para insertar datos en un conjunto de tablas empleado para dividir los datos de ventas de un año. Hay una tabla por cada mes del año, que tiene el formato siguiente:

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)
    )

Este procedimiento almacenado de ejemplo genera y ejecuta de forma dinámica una instrucción INSERT para insertar pedidos nuevos en la tabla que corresponda. En el ejemplo se utiliza la fecha de pedido para crear el nombre de la tabla que debe contener los datos y, a continuación, incorpora ese nombre a una instrucción INSERT.

[!NOTA] Éste es un ejemplo simple de sp_executesql. El ejemplo no contiene comprobación de errores ni incluye comprobaciones de reglas de negocios como, por ejemplo, garantizar que los números de pedido no se repitan en otras tablas.

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

En este procedimiento, el uso de sp_executesql resulta más eficaz que el de EXECUTE para ejecutar una cadena. Cuando se usa sp_executesql, sólo hay doce versiones de la cadena INSERT generada, una por cada tabla mensual. Con EXECUTE, cada cadena INSERT es única, ya que los valores de los parámetros son distintos. Aunque ambos métodos generan el mismo número de lotes, la semejanza de las cadenas INSERT que genera sp_executesql hace más probable que el optimizador de consultas vuelva a utilizar los planes de ejecución.

C. Utilizar el parámetro OUTPUT

En el ejemplo siguiente se utiliza un parámetro OUTPUT para almacenar el conjunto de resultados generado por la instrucción SELECT del parámetro @SQLString. Después se ejecutan dos instrucciones SELECT que utilizan el valor del parámetro OUTPUT.

USE AdventureWorks;
GO
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);
DECLARE @SalesOrderNumber nvarchar(25);
DECLARE @IntVariable int;
SET @SQLString = N'SELECT @SalesOrderOUT = MAX(SalesOrderNumber)
  FROM Sales.SalesOrderHeader
  WHERE CustomerID = @CustomerID';
SET @ParmDefinition = N'@CustomerID int,
                       @SalesOrderOUT nvarchar(25) OUTPUT';
SET @IntVariable = 22276;
EXECUTE sp_executesql
  @SQLString,
  @ParmDefinition,
  @CustomerID = @IntVariable,
  @SalesOrderOUT = @SalesOrderNumber OUTPUT;
-- This SELECT statement returns the value of the OUTPUT parameter.
SELECT @SalesOrderNumber;
-- This SELECT statement uses the value of the OUTPUT parameter in
-- the WHERE clause.
SELECT OrderDate, TotalDue
FROM Sales.SalesOrderHeader
WHERE SalesOrderNumber = @SalesOrderNumber;

Vea también

Referencia

EXECUTE (Transact-SQL)
Procedimientos almacenados del sistema (Transact-SQL)

Otros recursos

Lotes
Generar instrucciones en tiempo de ejecución

Ayuda e información

Obtener ayuda sobre SQL Server 2005

Historial de cambios