Compartir a través de

sp_executesql (Transact-SQL)

  • Artículo

Se aplica a: punto de conexión de SQL Server Azure SQL Database Azure SQL Instancia administrada punto de conexión de SQL Analytics Analytics Platform System (PDW) de SQL Analytics System (PDW) de SQL Server en Microsoft Fabric Warehouse en Microsoft Fabric

Ejecuta una instrucción Transact-SQL o un lote que se puede reutilizar muchas veces, o una que se compila dinámicamente. La instrucción o el lote de Transact-SQL puede contener parámetros insertados.

Precaución

Las instrucciones Transact-SQL compiladas en tiempo de ejecución pueden exponer aplicaciones a ataques malintencionados. Debe parametrizar las consultas al usar sp_executesql. Para obtener más información, consulte Inyección de código SQL.

Convenciones de sintaxis de Transact-SQL

Sintaxis

Sintaxis para SQL Server, Azure SQL Database, Azure SQL Instancia administrada, Azure Synapse Analytics y Analytics Platform System (PDW).

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

Los ejemplos de código de Transact-SQL de este artículo utilizan la base de datos de ejemplo AdventureWorks2022 o AdventureWorksDW2022, que se pueden descargar desde la página principal de Ejemplos y proyectos de la comunidad de Microsoft SQL Server.

Argumentos

[ @stmt = ] N'statement'

Cadena Unicode que contiene una instrucción Transact-SQL o un lote. @stmt debe ser una constante Unicode o una variable Unicode. No se permiten expresiones Unicode más complejas, como la concatenación de dos cadenas con el + operador . No se permiten constantes de caracteres. Las constantes Unicode deben tener Nel prefijo . Por ejemplo, la constante N'sp_who' Unicode es válida, pero la constante 'sp_who' de caracteres no es válida. 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).

@stmt pueden 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 definiciones de parámetros @params y en la lista de valores de parámetros.

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

Cadena que contiene las definiciones de todos los parámetros incrustados 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 más definiciones de parámetros. Todos los parámetros especificados en @stmt deben definirse en @params. Si la instrucción Transact-SQL o el lote de @stmt no contiene parámetros, no se requiere @params . El valor predeterminado para este parámetro es NULL.

[ @param1 = ] 'value1'

Valor del primer parámetro definido en la cadena de parámetros. El valor puede ser una constante Unicode o una variable Unicode. Debe haber un valor de parámetro proporcionado para cada parámetro incluido en @stmt. Los valores no son necesarios cuando la instrucción Transact-SQL o el lote 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 usar como OUTPUT parámetros, a menos que el procedimiento sea un procedimiento de Common Language Runtime (CLR). Un parámetro de salida que usa la OUTPUT palabra clave puede ser un marcador de posición de cursor, a menos que el procedimiento sea un procedimiento CLR.

[ ... n ]

Marcador de posición para los valores de parámetros adicionales. Los valores solo pueden ser constantes o variables. Los valores no pueden ser expresiones más complejas, como funciones o expresiones compiladas mediante operadores.

Valores de código de retorno

0 (correcto) o distinto de cero (error).

Conjunto de resultados

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

Comentarios

sp_executesql Los parámetros deben especificarse en el orden específico, tal como se describe en la sección Sintaxis anterior en este artículo. Si los parámetros se escriben fuera de orden, se produce 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 la base de datos. La instrucción Transact-SQL o el lote del sp_executesql parámetro @stmt no se compila hasta que se ejecuta la sp_executesql instrucción . A continuación, el contenido de @stmt se compila y ejecuta como un plan de ejecución independiente del plan de ejecución del lote que llamó a sp_executesql. El sp_executesql lote no puede hacer referencia a variables declaradas en el lote que llama a sp_executesql. Los cursores o variables locales del sp_executesql lote no son visibles para el lote que llama a sp_executesql. Los cambios en el contexto de base de datos solo se mantienen hasta el final de la instrucción sp_executesql .

sp_executesql se puede usar en lugar de procedimientos almacenados para ejecutar una instrucción Transact-SQL muchas veces cuando el cambio en los valores de parámetro a la instrucción es la única variación. Al permanecer constante la propia instrucción de 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. En este escenario, el rendimiento es equivalente al de un procedimiento almacenado.

Nota:

Para mejorar el rendimiento, use nombres de objeto completos en la cadena de instrucción.

sp_executesql admite la configuración de valores de parámetros por separado de la cadena de Transact-SQL, como se muestra en el ejemplo siguiente.

DECLARE @IntVariable INT;
DECLARE @SQLString NVARCHAR(500);
DECLARE @ParmDefinition NVARCHAR(500);

/* Build the SQL string once */
SET @SQLString = N'SELECT BusinessEntityID, NationalIDNumber, JobTitle, LoginID
       FROM AdventureWorks2022.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;

Los parámetros de salida también se pueden usar con sp_executesql. En el ejemplo siguiente se recupera un título de trabajo de la tabla de la HumanResources.Employee AdventureWorks2022 base de datos de ejemplo y se devuelve en el parámetro @max_titlede salida .

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 AdventureWorks2022.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;

Poder sustituir parámetros en sp_executesql ofrece las siguientes ventajas sobre el uso de la EXECUTE instrucción para ejecutar una cadena:

  • Dado que el texto real de la instrucción Transact-SQL de la sp_executesql cadena no cambia entre ejecuciones, el optimizador de consultas probablemente coincida con la instrucción Transact-SQL en la segunda ejecución con el plan de ejecución generado para la primera ejecución. Por lo tanto, SQL Server no tiene que compilar la segunda instrucción.

  • La cadena de Transact-SQL solo se compila una vez.

  • El parámetro de tipo integer se especifica en su formato nativo. No se requiere la conversión a Unicode.

Permisos

Debe pertenecer al rol public .

Ejemplos

A Ejecutar una instrucción SELECT

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

EXECUTE sp_executesql
    N'SELECT * FROM AdventureWorks2022.HumanResources.Employee
    WHERE BusinessEntityID = @level',
    N'@level TINYINT',
    @level = 109;

B. Ejecución de una cadena compilada dinámicamente

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 para 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 básico para sp_executesql. El ejemplo no contiene la comprobación de errores y no incluye comprobaciones de reglas de negocios, como garantizar que los números de pedido no estén duplicados entre 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

El uso sp_executesql de en este procedimiento es más eficaz que usar EXECUTE para ejecutar una cadena. Cuando sp_executesql se usa, solo hay 12 versiones de la INSERT cadena generadas, una para cada tabla mensual. Con EXECUTE, cada INSERT cadena es única porque los valores de parámetro son diferentes. Aunque ambos métodos generan el mismo número de lotes, la similitud de las INSERT cadenas generadas por sp_executesql hace que sea más probable que el optimizador de consultas reutilice los planes de ejecución.

C. Uso del parámetro OUTPUT

En el ejemplo siguiente se usa un OUTPUT parámetro para almacenar el conjunto de resultados generado por la SELECT instrucción en el @SQLString parámetro . A continuación, se ejecutan dos SELECT instrucciones que usan el valor del OUTPUT parámetro .

USE AdventureWorks2022;
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;

Ejemplos: Azure Synapse Analytics y Sistema de la plataforma de análisis (PDW)

D. Ejecutar una instrucción SELECT

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

EXECUTE sp_executesql
    N'SELECT * FROM AdventureWorksPDW2012.dbo.DimEmployee
    WHERE EmployeeKey = @level',
    N'@level TINYINT',
    @level = 109;

Contenido relacionado