sp_executesql (Transact-SQL)
Se aplica a:
SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)Punto de conexión de análisis SQL en Microsoft FabricAlmacenamiento en Microsoft Fabric
Ejecuta una instrucción o lote de 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 de Transact-SQL puede contener parámetros insertados.
Importante
Las instrucciones de Transact-SQL compiladas en tiempo de ejecución pueden exponer a las aplicaciones a ataques malintencionados.
Convenciones de sintaxis de Transact-SQL
Sintaxis
-- Syntax for SQL Server, Azure SQL Database, Azure Synapse Analytics, Parallel Data Warehouse
sp_executesql [ @stmt = ] statement
[
{ , [ @params = ] N'@parameter_name data_type [ OUT | OUTPUT ][ ,...n ]' }
{ , [ @param1 = ] 'value1' [ ,...n ] }
]
Argumentos
[ @stmt= ] statement
Es 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 +. Las constantes de caracteres no están permitidas. Si se especifica una constante Unicode, debe ir precedida de 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 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 insertado 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 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 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.
Observaciones
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 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 sp_executesql @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 solo 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 de 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 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.
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 de 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 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 utilizar con sp_executesql. En el ejemplo siguiente se recupera un título de trabajo de la tabla de la HumanResources.EmployeeAdventureWorks2022 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;
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 de Transact-SQL de la cadena de sp_executesql no cambia entre ejecuciones, es posible que el optimizador de consultas utilice la instrucción de 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
Debe pertenecer 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 AdventureWorks2022.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:
É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, solo 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 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 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 simple
En el siguiente ejemplo se crea y se ejecuta una instrucción SELECT simple que contiene un parámetro incrustado denominado @level.
-- Uses AdventureWorks2022
EXECUTE sp_executesql
N'SELECT * FROM AdventureWorksPDW2012.dbo.DimEmployee
WHERE EmployeeKey = @level',
N'@level TINYINT',
@level = 109;
Consulte también
EXECUTE (Transact-SQL)
Procedimientos almacenados del sistema (Transact-SQL)
Comentarios
Próximamente: A lo largo de 2024 iremos eliminando gradualmente GitHub Issues como mecanismo de comentarios sobre el contenido y lo sustituiremos por un nuevo sistema de comentarios. Para más información, vea: https://aka.ms/ContentUserFeedback.
Enviar y ver comentarios de