Compartir vía

sp_executesql (Transact-SQL)

  • Artículo

Se aplica a: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) Punto de conexión de análisis SQL en Microsoft Fabric Almacenamiento 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 SQL injection.

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'

Una cadena de Unicode que contiene una instrucción o lote 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 +. No se permiten constantes de caracteres. Las constantes Unicode deben tener Nel prefijo . Por ejemplo, la constante de Unicode N'sp_who' es válida, pero la constant de caracteres 'sp_who' 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 definición 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 definiciones de parámetros adicionales. 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'

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 haber un valor de parámetro proporcionado para cada parámetro incluido en @stmt. Los valores no son necesarios si la instrucción o el lote de Transact-SQL en @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 ]

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

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 desordenados, 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 AS INT;
DECLARE @SQLString AS NVARCHAR (500);
DECLARE @ParmDefinition AS 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 AS INT;

DECLARE @SQLString AS NVARCHAR (500);

DECLARE @ParmDefinition AS NVARCHAR (500);

DECLARE @max_title AS 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.

OPTIMIZED_SP_EXECUTESQL

Se aplica a: Azure SQL Database

Cuando la configuración con ámbito de base de datos de OPTIMIZED_SP_EXECUTESQL está habilitada, el comportamiento de compilación de lotes enviados mediante sp_executesql se convierte en idéntico al comportamiento de compilación serializado que emplean actualmente objetos como procedimientos almacenados y desencadenadores.

Cuando los lotes son idénticos (excepto las diferencias de parámetros), la OPTIMIZED_SP_EXECUTESQL opción intenta obtener un bloqueo de compilación como mecanismo de cumplimiento para garantizar que el proceso de compilación se serializa. Este bloqueo garantiza que, si varias sesiones invocan sp_executesql simultáneamente, esas sesiones esperarán al intentar obtener un bloqueo de compilación exclusivo después de que la primera sesión inicie el proceso de compilación. La primera ejecución de sp_executesql compila e inserta su plan compilado en la memoria caché del plan. Otras sesiones anulan la espera del bloqueo de compilación y reutilizan el plan una vez que esté disponible.

Sin la OPTIMIZED_SP_EXECUTESQL opción, varias invocaciones de lotes idénticos ejecutados a través sp_executesql de la compilación en paralelo y colocan sus propias copias de un plan compilado en la memoria caché del plan, que reemplazan o duplican las entradas de caché del plan en algunos casos.

Nota:

Antes de habilitar la OPTIMIZED_SP_EXECUTESQL configuración con ámbito de base de datos, si está habilitada la actualización automática de estadísticas, también debe habilitar la opción asincrónica de estadísticas de actualización automática con la opción de configuración con ámbito de base de datos ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY . Habilitar estas dos opciones puede reducir significativamente la probabilidad de que los problemas de rendimiento relacionados con tiempos de compilación largos, junto con bloqueos exclusivos excesivos del administrador de bloqueos (LCK_M_X) y WAIT_ON_SYNC_STATISTICS_REFRESH esperas.

OPTIMIZED_SP_EXECUTESQL está desactivado de forma predeterminada. Para habilitar OPTIMIZED_SP_EXECUTESQL en el nivel de base de datos, use la siguiente instrucción Transact-SQL:

ALTER DATABASE SCOPED CONFIGURATION
SET OPTIMIZED_SP_EXECUTESQL = ON;

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 AS NVARCHAR (500);
DECLARE @OrderMonth AS 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 la cadena compilada dinámicamente, ya que permite el uso de marcadores de parámetros. Los marcadores de parámetros hacen que sea más probable que el Motor de base de datos reutilice el plan de consulta generado, lo que ayuda a evitar compilaciones de consultas adicionales. Con EXECUTE, cada INSERT cadena es única porque los valores de parámetro son diferentes y se anexarían al final de la cadena generada dinámicamente. Cuando se ejecuta, la consulta no se parametrizaría de una manera que fomenta la reutilización del plan y tendría que compilarse antes de que se ejecute cada INSERT instrucción, lo que agregaría una entrada en caché independiente de la consulta en la memoria caché del plan.

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 AS NVARCHAR (500);
DECLARE @ParmDefinition AS NVARCHAR (500);
DECLARE @SalesOrderNumber AS NVARCHAR (25);
DECLARE @IntVariable AS 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