sp_executesql (Transact-SQL)sp_executesql (Transact-SQL)

SE APLICA A: síSQL Server (a partir de 2008) síAzure SQL Database síAzure SQL Data Warehouse síAlmacenamiento de datos paralelos APPLIES TO: yesSQL Server (starting with 2008) yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

Ejecuta una instrucción o lote Transact-SQLTransact-SQL que puede volver a utilizarse muchas veces o uno que se ha generado de forma dinámica.Executes a Transact-SQLTransact-SQL statement or batch that can be reused many times, or one that has been built dynamically. La instrucción o el lote Transact-SQLTransact-SQL puede contener parámetros incrustados.The Transact-SQLTransact-SQL statement or batch can contain embedded parameters.

Importante

Las instrucciones Transact-SQLTransact-SQL compiladas en tiempo de ejecución pueden exponer a las aplicaciones a ataques malintencionados.Run time-compiled Transact-SQLTransact-SQL statements can expose applications to malicious attacks.

Icono de vínculo de tema Convenciones de sintaxis de Transact-SQLTopic link icon Transact-SQL Syntax Conventions

SintaxisSyntax

-- Syntax for SQL Server, Azure SQL Database, Azure SQL Data Warehouse, Parallel Data Warehouse  

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

ArgumentosArguments

[ @stmt= ] statement[ @stmt= ] statement
Es una cadena Unicode que contiene un Transact-SQLTransact-SQL instrucción o lote.Is a Unicode string that contains a Transact-SQLTransact-SQL statement or batch. @stmt debe ser una constante Unicode o una variable Unicode.@stmt must be either a Unicode constant or a Unicode variable. No se permite utilizar expresiones Unicode más complejas, como una concatenación de dos cadenas con el operador +.More complex Unicode expressions, such as concatenating two strings with the + operator, are not allowed. Las constantes de caracteres no están permitidas.Character constants are not allowed. Si se especifica una constante Unicode, deben ir precedido por un N. Por ejemplo, la constante Unicode N 'sp_who' es válido, pero la constante de caracteres 'sp_who' no es.If a Unicode constant is specified, it must be prefixed with an N. For example, the Unicode constant N'sp_who' is valid, but the character constant 'sp_who' is not. El tamaño de la cadena solo está limitado por la memoria disponible en el servidor de bases de datos.The size of the string is limited only by available database server memory. En los servidores de 64 bits, el tamaño de la cadena se limita a 2 GB, el tamaño máximo de nvarchar (max).On 64-bit servers, the size of the string is limited to 2 GB, the maximum size of 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'@stmt can contain parameters having the same form as a variable name, for example: N'SELECT * FROM HumanResources.Employee WHERE EmployeeID = @IDParameter'

Cada parámetro incluido en @stmt debe tener una entrada correspondiente tanto en el @params lista de definiciones de parámetro y el parámetro de la lista de valores.Each parameter included in @stmt must have a corresponding entry in both the @params parameter definition list and the parameter values list.

[ @params= ] N'@parameter_name**data_type [ ,... n ] '[ @params= ] N'@parameter_name**data_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.Is one string that contains the definitions of all parameters that have been embedded in @stmt. The string must be either a Unicode constant or a Unicode variable. Cada definición de parámetro se compone de un nombre de parámetro y un tipo de datos.Each parameter definition consists of a parameter name and a data type. n es un marcador de posición que indica definiciones de parámetros adicionales.n is a placeholder that indicates additional parameter definitions. Todos los parámetros especificados en @stmtmust definirse en @params.Every parameter specified in @stmtmust be defined in @params. Si el Transact-SQLTransact-SQL instrucción o lote en @stmt no contiene parámetros, @params no es necesario.If the Transact-SQLTransact-SQL statement or batch in @stmt does not contain parameters, @params is not required. El valor predeterminado de este parámetro es NULL.The default value for this parameter is NULL.

[ @param1= ] 'value1'[ @param1= ] 'value1'
Es un valor para el primer parámetro definido en la cadena de parámetros.Is a value for the first parameter that is defined in the parameter string. El valor puede ser una constante Unicode o una variable Unicode.The value can be a Unicode constant or a Unicode variable. Debe haber un valor de parámetro proporcionado para cada parámetro incluido en @stmt. Los valores no son necesarios cuando el Transact-SQLTransact-SQL instrucción o lote en @stmt no tiene ningún parámetro.There must be a parameter value supplied for every parameter included in @stmt. The values are not required when the Transact-SQLTransact-SQL statement or batch in @stmt has no parameters.

[ OUT | OUTPUT ][ OUT | OUTPUT ]
Indica que se trata de un parámetro de salida.Indicates that the parameter is an output parameter. texto, ntext, y imagen parámetros se pueden usar como parámetros OUTPUT, a menos que el procedimiento sea un procedimiento de common language runtime (CLR).text, ntext, and image parameters can be used as OUTPUT parameters, unless the procedure is a common language runtime (CLR) procedure. 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.An output parameter that uses the OUTPUT keyword can be a cursor placeholder, unless the procedure is a CLR procedure.

nn
Es un marcador de posición para los valores de los parámetros adicionales.Is a placeholder for the values of additional parameters. Los valores solo pueden ser constantes o variables.Values can only be constants or variables. Los valores no pueden ser expresiones más complejas como funciones ni expresiones generadas mediante operadores.Values cannot be more complex expressions such as functions, or expressions built by using operators.

Valores de código de retornoReturn Code Values

0 (correcto) o distinto de cero (error)0 (success) or non-zero (failure)

Conjuntos de resultadosResult Sets

Devuelve los conjuntos de resultados de todas las instrucciones SQL integradas en la cadena SQL.Returns the result sets from all the SQL statements built into the SQL string.

ComentariosRemarks

parámetros de sp_executesql deben escribirse en el orden específico, tal como se describe en la sección "Sintaxis" anteriormente en este tema.sp_executesql parameters must be entered in the specific order as described in the "Syntax" section earlier in this topic. Si los parámetros se escriben desordenados, se producirá un mensaje de error.If the parameters are entered out of order, an error message will occur.

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.sp_executesql has the same behavior as EXECUTE with regard to batches, the scope of names, and database context. El Transact-SQLTransact-SQL instrucción o lote de sp_executesql @parámetro stmt no se compila hasta que se ejecuta la instrucción sp_executesql.The Transact-SQLTransact-SQL statement or batch in the sp_executesql @stmt parameter is not compiled until the sp_executesql statement is executed. El contenido de @stmt, a continuación, se compila y se ejecuta como un plan de ejecución independiente del plan de ejecución del lote que llama a sp_executesql.The contents of @stmt are then compiled and executed as an execution plan separate from the execution plan of the batch that called sp_executesql. El lote de sp_executesql no puede hacer referencia a las variables declaradas en el lote que llama a sp_executesql.The sp_executesql batch cannot reference variables declared in the batch that calls sp_executesql. Los cursores o las variables locales del lote de sp_executesql no son visibles para el lote que llama a sp_executesql.Local cursors or variables in the sp_executesql batch are not visible to the batch that calls sp_executesql. Los cambios en el contexto de base de datos solo se mantienen hasta el final de la instrucción sp_executesql.Changes in database context last only to the end of the sp_executesql statement.

sp_executesql puede utilizarse como alternativa a los procedimientos almacenados para ejecutar varias veces una instrucción Transact-SQLTransact-SQL si la única variación es que cambian los valores de los parámetros de la instrucción.sp_executesql can be used instead of stored procedures to execute a Transact-SQLTransact-SQL statement many times when the change in parameter values to the statement is the only variation. Al permanecer constante la propia instrucción Transact-SQLTransact-SQL y variar solo los valores de los parámetros, es probable que el optimizador de consultas de SQL ServerSQL Server vuelva a utilizar el plan de ejecución que genera para la primera ejecución.Because the Transact-SQLTransact-SQL statement itself remains constant and only the parameter values change, the SQL ServerSQL Server query optimizer is likely to reuse the execution plan it generates for the first execution.

Nota

Para mejorar el rendimiento, utilice nombres de objeto completos en la cadena de la instrucción.To improve performance use fully qualified object names in the statement string.

sp_executesql permite establecer los valores de los parámetros independientemente de la cadena Transact-SQLTransact-SQL, como se muestra en el siguiente ejemplo.sp_executesql supports the setting of parameter values separately from the Transact-SQLTransact-SQL string as shown in the following example.

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;  

Los parámetros de salida también se pueden utilizar con sp_executesql.Output parameters can also be used with 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.The following example retrieves a job title from the AdventureWorks2012.HumanResources.Employee table and returns it in the output parameter @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:Being able to substitute parameters in sp_executesql offers the following advantages to using the EXECUTE statement to execute a string:

  • Debido a que el texto real de la instrucción Transact-SQLTransact-SQL de la cadena de sp_executesql no cambia entre ejecuciones, es posible que el optimizador de consultas utilice la instrucción Transact-SQLTransact-SQL de la segunda ejecución con el plan de ejecución generado en la primera.Because the actual text of the Transact-SQLTransact-SQL statement in the sp_executesql string does not change between executions, the query optimizer will probably match the Transact-SQLTransact-SQL statement in the second execution with the execution plan generated for the first execution. De este modo, SQL ServerSQL Server no tiene que compilar la segunda instrucción.Therefore, SQL ServerSQL Server does not have to compile the second statement.

  • La cadena de Transact-SQLTransact-SQL solo se genera una vez.The Transact-SQLTransact-SQL string is built only one time.

  • El parámetro de tipo integer se especifica en su formato nativo.The integer parameter is specified in its native format. No es necesaria la conversión a Unicode.Casting to Unicode is not required.

PermisosPermissions

Debe pertenecer al rol public.Requires membership in the public role.

EjemplosExamples

A.A. Ejecutar una instrucción SELECT simpleExecuting a simple SELECT statement

En el siguiente ejemplo se crea y se ejecuta una instrucción SELECT simple que contiene un parámetro incrustado denominado @level.The following example creates and executes a simple SELECT statement that contains an embedded parameter named @level.

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

b.B. Ejecutar una cadena generada de forma dinámicaExecuting a dynamically built string

En el siguiente ejemplo se muestra el uso de sp_executesql para ejecutar una cadena generada de forma dinámica.The following example shows using sp_executesql to execute a dynamically built string. 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.The example stored procedure is used to insert data into a set of tables that are used to partition sales data for a year. Hay una tabla por cada mes del año, que tiene el formato siguiente:There is one table for each month of the year that has the following format:

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.This sample stored procedure dynamically builds and executes an INSERT statement to insert new orders into the correct table. 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.The example uses the order date to build the name of the table that should contain the data, and then incorporates that name into an INSERT statement.

Nota

Éste es un ejemplo simple de sp_executesql.This is a simple example for 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.The example does not contain error checking and does not include checks for business rules, such as guaranteeing that order numbers are not duplicated between tables.

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.Using sp_executesql in this procedure is more efficient than using EXECUTE to execute a string. Cuando se usa sp_executesql, solo hay doce versiones de la cadena INSERT generada, una por cada tabla mensual.When sp_executesql is used, there are only 12 versions of the INSERT string that are generated, one for each monthly table. Con EXECUTE, cada cadena INSERT es única, ya que los valores de los parámetros son distintos.With EXECUTE, each INSERT string is unique because the parameter values are different. 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.Although both methods generate the same number of batches, the similarity of the INSERT strings generated by sp_executesql makes it more likely that the query optimizer will reuse execution plans.

C.C. Utilizar el parámetro OUTPUTUsing the OUTPUT Parameter

En el ejemplo siguiente se usa un OUTPUT parámetro para almacenar el conjunto de resultados generado por el SELECT instrucción en el @SQLString parámetro. Dos SELECT , a continuación, se ejecutan las instrucciones que usan el valor de la OUTPUT parámetro.The following example uses an OUTPUT parameter to store the result set generated by the SELECT statement in the @SQLString parameter.Two SELECT statements are then executed that use the value of the OUTPUT parameter.

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;  

Ejemplos: Almacenamiento de datos SQL de AzureAzure SQL Data Warehouse y Almacenamiento de datos paralelosParallel Data WarehouseExamples: Almacenamiento de datos SQL de AzureAzure SQL Data Warehouse and Almacenamiento de datos paralelosParallel Data Warehouse

D.D. Ejecutar una instrucción SELECT simpleExecuting a simple SELECT statement

En el siguiente ejemplo se crea y se ejecuta una instrucción SELECT simple que contiene un parámetro incrustado denominado @level.The following example creates and executes a simple SELECT statement that contains an embedded parameter named @level.

-- Uses AdventureWorks  

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

Para obtener ejemplos adicionales, consulte sp_executesql (Transact-SQL).For additional examples, see sp_executesql (Transact-SQL).

Vea tambiénSee Also

EXECUTE (Transact-SQL) EXECUTE (Transact-SQL)
Procedimientos almacenados del sistema (Transact-SQL)System Stored Procedures (Transact-SQL)