sp_executesql (Transact-SQL)

  • Artículo

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.

Nota de seguridadNota de seguridad

Las instrucciones Transact-SQL compiladas en tiempo de ejecución pueden exponer a las aplicaciones a ataques malintencionados.

Icono de vínculo a temas Convenciones de sintaxis de Transact-SQL

Sintaxis

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

Argumentos

  • [ @stmt= ] statement
    Es una cadena Unicode que contiene un lote o una instrucción 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 solo 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 tanto en la lista de definición de parámetros @params como 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 @stmtse deben definir en @params. Si la instrucción o el lote de Transact-SQL en @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 solo pueden ser constantes o variables. Los valores no pueden ser expresiones más complejas como funciones ni expresiones generadas mediante operadores.

Valores de código de retorno

0 (correcto) o distinto de cero (error)

Conjuntos de resultados

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

Comentarios

Los parámetros sp_executesql se deben escribir en el orden específico descrito en la sección "Sintaxis" anterior de este tema. Si los parámetros se escriben desordenados, se producirá un mensaje de error.

sp_executesql tiene el mismo comportamiento que EXECUTE con respecto a los lotes, el ámbito de los nombres y el contexto de las bases de datos. La instrucción o el lote Transact-SQL del parámetro sp_executesql @stmt no se compila hasta que se ejecuta la instrucción sp_executesql. El contenido de @stmt se compila entonces y se ejecuta como un plan de ejecución independiente del plan de ejecución del lote que realizó la llamada a sp_executesql. El lote sp_executesql no puede hacer referencia a las variables declaradas en el lote que llama a sp_executesql. Los cursores o las variables locales en el lote sp_executesql no son visibles para el lote que llama a sp_executesql. Los cambios en el contexto de la base de datos solo duran 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 el cambio de valores de los parámetros de la instrucción. Al permanecer constante la propia instrucción Transact-SQL y variar solo 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 BusinessEntityID, NationalIDNumber, JobTitle, LoginID
       FROM AdventureWorks2012.HumanResources.Employee 
       WHERE BusinessEntityID = @BusinessEntityID';
SET @ParmDefinition = N'@BusinessEntityID tinyint';
/* Execute the string with the first parameter value. */
SET @IntVariable = 197;
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @BusinessEntityID = @IntVariable;
/* Execute the same string with the second parameter value. */
SET @IntVariable = 109;
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @BusinessEntityID = @IntVariable;

También se pueden utilizar parámetros de salida con sp_executesql. En el ejemplo siguiente, se recupera un puesto de trabajo de la tabla AdventureWorks2012.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(JobTitle) 
   FROM AdventureWorks2012.HumanResources.Employee
   WHERE BusinessEntityID = @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 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 solo 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

Requiere la pertenencia al rol public.

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 AdventureWorks2012.HumanResources.Employee 
          WHERE BusinessEntityID = @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]

Este es un ejemplo simple para 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 eficiente que el de EXECUTE para ejecutar una cadena. Cuando se usa sp_executesql, solo se generan doce versiones de la cadena INSERT, 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 siguiente ejemplo se utiliza un parámetro OUTPUT para almacenar el conjunto de resultados que genera la instrucción SELECT en el parámetro @SQLString. Posteriormente, se ejecutan dos instrucciones SELECT las cuales utilizan el valor del parámetro OUTPUT.

USE AdventureWorks2012;
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)