Parámetros con valores de tablaTable-Valued Parameters

Los parámetros con valores de tabla proporcionan un método sencillo para calcular las referencias de varias filas de datos procedentes de una aplicación cliente en SQL Server sin necesidad de efectuar viajes de ida y vuelta (round trip) ni de crear lógica especial de servidor para procesar los datos.Table-valued parameters provide an easy way to marshal multiple rows of data from a client application to SQL Server without requiring multiple round trips or special server-side logic for processing the data. Puede usar los parámetros con valores de tabla para encapsular las filas de datos de una aplicación cliente y enviar los datos al servidor en un único comando con parámetros.You can use table-valued parameters to encapsulate rows of data in a client application and send the data to the server in a single parameterized command. Las filas de datos de entrada se almacenan en una variable de tabla en la que se puede operar utilizando Transact-SQL.The incoming data rows are stored in a table variable that can then be operated on by using Transact-SQL.

El acceso a los valores de columna de los parámetros con valores de tabla se realiza con instrucciones estándar SELECT de Transact-SQL.Column values in table-valued parameters can be accessed using standard Transact-SQL SELECT statements. Los parámetros con valores de tabla están fuertemente tipados y su estructura se valida automáticamente.Table-valued parameters are strongly typed and their structure is automatically validated. El tamaño de los parámetros con valores de tabla está únicamente limitado por la memoria del servidor.The size of table-valued parameters is limited only by server memory.

Nota

No puede devolver datos de un parámetro con valor de tabla.You cannot return data in a table-valued parameter. Los parámetros con valores de tabla son sólo de entrada; no se admite la palabra clave OUTPUT.Table-valued parameters are input-only; the OUTPUT keyword is not supported.

Para obtener más información sobre parámetros con valores de tabla, vea los siguientes recursos.For more information about table-valued parameters, see the following resources.

RecursoResource DESCRIPCIÓNDescription
Parámetros con valores de tabla (motor de base de datos) en libros en pantalla de SQL ServerTable-Valued Parameters (Database Engine) in SQL Server Books Online Describe cómo se crean y se usan los parámetros con valores de tabla.Describes how to create and use table-valued parameters.
Tipos de tabla definidos por el usuario en libros en pantalla de SQL ServerUser-Defined Table Types in SQL Server Books Online Describe los tipos de tabla definidos por el usuario que se usan para declarar parámetros con valores de tabla.Describes user-defined table types that are used to declare table-valued parameters.

Pasar varias filas de versiones previas de SQL ServerPassing Multiple Rows in Previous Versions of SQL Server

Antes de que los parámetros con valores de tabla se introdujeron en SQL Server 2008, las opciones para pasar varias filas de datos a un procedimiento almacenado o a un comando SQL con parámetros eran limitadas.Before table-valued parameters were introduced to SQL Server 2008, the options for passing multiple rows of data to a stored procedure or a parameterized SQL command were limited. Un programador podía elegir entre las siguientes opciones para pasar varias filas al servidor:A developer could choose from the following options for passing multiple rows to the server:

  • Usar una serie de parámetros individuales para representar los valores en varias columnas y filas de datos.Use a series of individual parameters to represent the values in multiple columns and rows of data. La cantidad de datos que se pueden pasar mediante este método está limitada por el número de parámetros permitidos.The amount of data that can be passed by using this method is limited by the number of parameters allowed. Los procedimientos de SQL Server pueden tener 2100 parámetros como máximo.SQL Server procedures can have, at most, 2100 parameters. La lógica de servidor es necesaria para ensamblar estos valores individuales en una variable de tabla o en una tabla temporal para su procesamiento.Server-side logic is required to assemble these individual values into a table variable or a temporary table for processing.

  • Empaquetar varios valores de datos en cadenas delimitadas o documentos XML y, a continuación, pasar esos valores de texto a un procedimiento o instrucción.Bundle multiple data values into delimited strings or XML documents and then pass those text values to a procedure or statement. Por ello, el procedimiento o la instrucción deben incluir la lógica necesaria para validar las estructuras de datos y desempaquetar los valores.This requires the procedure or statement to include the logic necessary for validating the data structures and unbundling the values.

  • Crear una serie de instrucciones SQL individuales para las notificaciones de datos que afecten varias filas, como las creadas mediante la llamada al método Update de SqlDataAdapter.Create a series of individual SQL statements for data modifications that affect multiple rows, such as those created by calling the Update method of a SqlDataAdapter. Los cambios se pueden enviar individualmente o por lotes en grupos al servidor.Changes can be submitted to the server individually or batched into groups. Sin embargo, aunque se envíen por lotes que contengan varias instrucciones, cada instrucción se ejecuta por separado en el servidor.However, even when submitted in batches that contain multiple statements, each statement is executed separately on the server.

  • Usar la utilidad bcp o el objeto SqlBulkCopy para cargar muchas filas de datos en una tabla.Use the bcp utility program or the SqlBulkCopy object to load many rows of data into a table. Aunque esta técnica sea muy eficaz, no es compatible con el procesamiento de servidor a menos que los datos se carguen en una tabla temporal o en una variable de tabla.Although this technique is very efficient, it does not support server-side processing unless the data is loaded into a temporary table or table variable.

Crear tipos de parámetros con valores de tablaCreating Table-Valued Parameter Types

Los parámetros con valores de tabla se basan en estructuras de tabla fuertemente tipadas definidas mediante instrucciones CREATE TYPE de Transact-SQL.Table-valued parameters are based on strongly-typed table structures that are defined by using Transact-SQL CREATE TYPE statements. Debe crear un tipo de tabla y definir la estructura en SQL Server antes de poder usar los parámetros con valores de tabla en las aplicaciones cliente.You have to create a table type and define the structure in SQL Server before you can use table-valued parameters in your client applications. Para obtener más información sobre la creación de tipos de tabla, vea tipos de tabla definidos por el usuario en libros en pantalla de SQL Server.For more information about creating table types, see User-Defined Table Types in SQL Server Books Online.

La siguiente instrucción crea un tipo de tabla denominado CategoryTableType formada por las columnas CategoryID y CategoryName:The following statement creates a table type named CategoryTableType that consists of CategoryID and CategoryName columns:

CREATE TYPE dbo.CategoryTableType AS TABLE  
    ( CategoryID int, CategoryName nvarchar(50) )  

Después de crear un tipo de tabla, puede declarar los parámetros con valores de tabla basados en ese tipo.After you create a table type, you can declare table-valued parameters based on that type. El siguiente fragmento de Transact-SQL muestra cómo declarar un parámetro con valores de tabla en una definición de procedimiento almacenado.The following Transact-SQL fragment demonstrates how to declare a table-valued parameter in a stored procedure definition. Observe que se requiere la palabra clave READONLY para declarar un parámetro con valores de tabla.Note that the READONLY keyword is required for declaring a table-valued parameter.

CREATE PROCEDURE usp_UpdateCategories   
    (@tvpNewCategories dbo.CategoryTableType READONLY)  

Modificar datos con parámetros con valores de tabla (Transact-SQL)Modifying Data with Table-Valued Parameters (Transact-SQL)

Los parámetros con valores de tabla se pueden usar en modificaciones de datos basados en conjuntos que afectan varias filas mediante la ejecución de una sola instrucción.Table-valued parameters can be used in set-based data modifications that affect multiple rows by executing a single statement. Por ejemplo, puede seleccionar todas las filas de un parámetro con valores de tabla e insertarlas en una tabla de base de datos o crear una instrucción de actualización mediante la combinación de un parámetro con valores de tabla y la tabla que desee actualizar.For example, you can select all the rows in a table-valued parameter and insert them into a database table, or you can create an update statement by joining a table-valued parameter to the table you want to update.

La siguiente instrucción UPDATE de Transact-SQL muestra cómo usar un parámetro con valores de tabla mediante su unión con la tabla Categories.The following Transact-SQL UPDATE statement demonstrates how to use a table-valued parameter by joining it to the Categories table. Cuando use un parámetro con valores de tabla con JOIN en una cláusula FROM, también debe asignarle un alias, como se muestra aquí, donde el parámetro con valores de tabla tiene el alias "ec":When you use a table-valued parameter with a JOIN in a FROM clause, you must also alias it, as shown here, where the table-valued parameter is aliased as "ec":

UPDATE dbo.Categories  
    SET Categories.CategoryName = ec.CategoryName  
    FROM dbo.Categories INNER JOIN @tvpEditedCategories AS ec  
    ON dbo.Categories.CategoryID = ec.CategoryID;  

Este ejemplo de Transact-SQL muestra cómo seleccionar en un parámetro con valores de tabla para ejecutar INSERT en una sola operación basada en conjuntos.This Transact-SQL example demonstrates how to select rows from a table-valued parameter to perform an INSERT in a single set-based operation.

INSERT INTO dbo.Categories (CategoryID, CategoryName)  
    SELECT nc.CategoryID, nc.CategoryName FROM @tvpNewCategories AS nc;  

Limitaciones de los parámetros con valores de tablaLimitations of Table-Valued Parameters

Existen varias limitaciones en los parámetros con valores de tabla:There are several limitations to table-valued parameters:

  • No se pueden pasar parámetros con valores de tabla a funciones definidas por el usuario de CLR.You cannot pass table-valued parameters to CLR user-defined functions.

  • Los parámetros con valores de tabla solo se pueden indizar para admitir restricciones UNIQUE o PRIMARY KEY.Table-valued parameters can only be indexed to support UNIQUE or PRIMARY KEY constraints. SQL Server no mantiene estadísticas de parámetros con valores de tabla.SQL Server does not maintain statistics on table-valued parameters.

  • Los parámetros con valores de tabla son de solo lectura en el código Transact-SQL.Table-valued parameters are read-only in Transact-SQL code. No puede actualizar los valores de columna de las filas de un parámetro con valores de tabla ni insertar ni eliminar filas.You cannot update the column values in the rows of a table-valued parameter and you cannot insert or delete rows. Para modificar los datos que se pasan a un procedimiento almacenado o a una instrucción con parámetros de un parámetro con valores de tabla, debe insertar los datos en una tabla temporal o en una variable de tabla.To modify the data that is passed to a stored procedure or parameterized statement in table-valued parameter, you must insert the data into a temporary table or into a table variable.

  • No puede usar instrucciones ALTER TABLE para modificar el diseño de los parámetros con valores de tabla.You cannot use ALTER TABLE statements to modify the design of table-valued parameters.

Configurar un ejemplo de SqlParameterConfiguring a SqlParameter Example

System.Data.SqlClientpermite rellenar los parámetros con DataTablevalores DbDataReader de IEnumerable<T> tabla de los objetos o \ SqlDataRecord .System.Data.SqlClient supports populating table-valued parameters from DataTable, DbDataReader or IEnumerable<T> \ SqlDataRecord objects. Debe especificar un nombre de tipo para el parámetro con valores de tabla mediante la propiedad TypeName de una clase SqlParameter.You must specify a type name for the table-valued parameter by using the TypeName property of a SqlParameter. El valor de TypeName debe coincidir con el nombre de un tipo compatible previamente creado en el servidor.The TypeName must match the name of a compatible type previously created on the server. El fragmento de código siguiente muestra cómo se configura SqlParameter para insertar datos.The following code fragment demonstrates how to configure SqlParameter to insert data.

En el ejemplo siguiente, la addedCategories variable contiene un DataTable.In the following example, the addedCategories variable contains a DataTable. Para ver cómo se rellena la variable, vea los ejemplos de la sección siguiente, pasar un parámetro con valores de tabla a un procedimiento almacenado.To see how the variable is populated, see the examples in the next section, Passing a Table-Valued Parameter to a Stored Procedure.

// Configure the command and parameter.  
SqlCommand insertCommand = new SqlCommand(sqlInsert, connection);  
SqlParameter tvpParam = insertCommand.Parameters.AddWithValue("@tvpNewCategories", addedCategories);  
tvpParam.SqlDbType = SqlDbType.Structured;  
tvpParam.TypeName = "dbo.CategoryTableType";  
' Configure the command and parameter.  
Dim insertCommand As New SqlCommand(sqlInsert, connection)  
Dim tvpParam As SqlParameter = _  
   insertCommand.Parameters.AddWithValue( _  
  "@tvpNewCategories", addedCategories)  
tvpParam.SqlDbType = SqlDbType.Structured  
tvpParam.TypeName = "dbo.CategoryTableType"  

Además puede usar cualquier objeto derivado de DbDataReader para transmitir filas de datos por secuencias a un parámetro con valores de tabla, como se muestra en este fragmento:You can also use any object derived from DbDataReader to stream rows of data to a table-valued parameter, as shown in this fragment:

// Configure the SqlCommand and table-valued parameter.  
SqlCommand insertCommand = new SqlCommand("usp_InsertCategories", connection);  
insertCommand.CommandType = CommandType.StoredProcedure;  
SqlParameter tvpParam = insertCommand.Parameters.AddWithValue("@tvpNewCategories", dataReader);  
tvpParam.SqlDbType = SqlDbType.Structured;  
' Configure the SqlCommand and table-valued parameter.  
Dim insertCommand As New SqlCommand("usp_InsertCategories", connection)  
insertCommand.CommandType = CommandType.StoredProcedure  
Dim tvpParam As SqlParameter = _  
  insertCommand.Parameters.AddWithValue("@tvpNewCategories", _  
  dataReader)  
tvpParam.SqlDbType = SqlDbType.Structured  

Pasar un parámetro con valores de tabla a un procedimiento almacenadoPassing a Table-Valued Parameter to a Stored Procedure

Este ejemplo muestra cómo pasar datos de parámetros con valores de tabla a un procedimiento almacenado.This example demonstrates how to pass table-valued parameter data to a stored procedure. El código extrae las filas agregadas en un nuevo elemento DataTable mediante el uso del método GetChanges.The code extracts added rows into a new DataTable by using the GetChanges method. Después, el código define SqlCommand, estableciendo el valor de la propiedad CommandType en StoredProcedure.The code then defines a SqlCommand, setting the CommandType property to StoredProcedure. SqlParameter se rellena mediante el método AddWithValue y la propiedad SqlDbType se establece en Structured.The SqlParameter is populated by using the AddWithValue method and the SqlDbType is set to Structured. SqlCommand se ejecuta luego mediante el método ExecuteNonQuery.The SqlCommand is then executed by using the ExecuteNonQuery method.

// Assumes connection is an open SqlConnection object.  
using (connection)  
{  
  // Create a DataTable with the modified rows.  
  DataTable addedCategories = CategoriesDataTable.GetChanges(DataRowState.Added);  

  // Configure the SqlCommand and SqlParameter.  
  SqlCommand insertCommand = new SqlCommand("usp_InsertCategories", connection);  
  insertCommand.CommandType = CommandType.StoredProcedure;  
  SqlParameter tvpParam = insertCommand.Parameters.AddWithValue("@tvpNewCategories", addedCategories);  
  tvpParam.SqlDbType = SqlDbType.Structured;  

  // Execute the command.  
  insertCommand.ExecuteNonQuery();  
}  
' Assumes connection is an open SqlConnection object.  
Using connection  
   '  Create a DataTable with the modified rows.  
   Dim addedCategories As DataTable = _  
     CategoriesDataTable.GetChanges(DataRowState.Added)  
  
  ' Configure the SqlCommand and SqlParameter.  
   Dim insertCommand As New SqlCommand( _  
     "usp_InsertCategories", connection)  
   insertCommand.CommandType = CommandType.StoredProcedure  
   Dim tvpParam As SqlParameter = _  
     insertCommand.Parameters.AddWithValue( _  
     "@tvpNewCategories", addedCategories)  
   tvpParam.SqlDbType = SqlDbType.Structured  
  
   '  Execute the command.  
   insertCommand.ExecuteNonQuery()  
End Using  

Pasar un parámetro con valores de tabla a una instrucción SQL con parámetrosPassing a Table-Valued Parameter to a Parameterized SQL Statement

El siguiente ejemplo muestra cómo insertar datos en la tabla dbo.Categories mediante una instrucción INSERT con una subconsulta SELECT que tiene un parámetro con valores de tabla como el origen de los datos.The following example demonstrates how to insert data into the dbo.Categories table by using an INSERT statement with a SELECT subquery that has a table-valued parameter as the data source. Cuando se pasa un parámetro con valores de tabla a una instrucción SQL con parámetros, debe especificar un nombre de tipo para dicho parámetro mediante el uso de la nueva propiedad TypeName de SqlParameter.When passing a table-valued parameter to a parameterized SQL statement, you must specify a type name for the table-valued parameter by using the new TypeName property of a SqlParameter. Este valor de TypeName debe coincidir con el nombre de un tipo compatible previamente creado en el servidor.This TypeName must match the name of a compatible type previously created on the server. El código de este ejemplo usa la propiedad TypeName para hacer referencia a la estructura de tipos definida en dbo.CategoryTableType.The code in this example uses the TypeName property to reference the type structure defined in dbo.CategoryTableType.

Nota

Si proporciona un valor para una columna de identidad de un parámetro con valores de tabla, debe emitir la instrucción SET IDENTITY_INSERT de la sesión.If you supply a value for an identity column in a table-valued parameter, you must issue the SET IDENTITY_INSERT statement for the session.

// Assumes connection is an open SqlConnection.  
using (connection)  
{  
  // Create a DataTable with the modified rows.  
  DataTable addedCategories = CategoriesDataTable.GetChanges(DataRowState.Added);  

  // Define the INSERT-SELECT statement.  
  string sqlInsert =   
      "INSERT INTO dbo.Categories (CategoryID, CategoryName)"  
      + " SELECT nc.CategoryID, nc.CategoryName"  
      + " FROM @tvpNewCategories AS nc;"  

  // Configure the command and parameter.  
  SqlCommand insertCommand = new SqlCommand(sqlInsert, connection);  
  SqlParameter tvpParam = insertCommand.Parameters.AddWithValue("@tvpNewCategories", addedCategories);  
  tvpParam.SqlDbType = SqlDbType.Structured;  
  tvpParam.TypeName = "dbo.CategoryTableType";  

  // Execute the command.  
  insertCommand.ExecuteNonQuery();  
}  
' Assumes connection is an open SqlConnection.  
Using connection  
  ' Create a DataTable with the modified rows.  
  Dim addedCategories As DataTable = _  
    CategoriesDataTable.GetChanges(DataRowState.Added)  
  
  ' Define the INSERT-SELECT statement.  
  Dim sqlInsert As String = _  
  "INSERT INTO dbo.Categories (CategoryID, CategoryName)" _  
  & " SELECT nc.CategoryID, nc.CategoryName" _  
  & " FROM @tvpNewCategories AS nc;"  
  
  ' Configure the command and parameter.  
  Dim insertCommand As New SqlCommand(sqlInsert, connection)  
  Dim tvpParam As SqlParameter = _  
     insertCommand.Parameters.AddWithValue( _  
    "@tvpNewCategories", addedCategories)  
  tvpParam.SqlDbType = SqlDbType.Structured  
  tvpParam.TypeName = "dbo.CategoryTableType"  
  
  ' Execute the query  
  insertCommand.ExecuteNonQuery()  
End Using  

Transmitir filas por secuencias con un DataReaderStreaming Rows with a DataReader

Asimismo puede usar cualquier objeto derivado de DbDataReader para transmitir filas de datos por secuencias a un parámetro con valores de tabla.You can also use any object derived from DbDataReader to stream rows of data to a table-valued parameter. El siguiente fragmento de código muestra la recuperación de datos de una base de datos de Oracle mediante el uso de OracleCommand y OracleDataReader.The following code fragment demonstrates retrieving data from an Oracle database by using an OracleCommand and an OracleDataReader. Después el código configura un elemento SqlCommand para invocar un procedimiento almacenado con un solo parámetro de entrada.The code then configures a SqlCommand to invoke a stored procedure with a single input parameter. La propiedad SqlDbType de SqlParameter se establece en Structured.The SqlDbType property of the SqlParameter is set to Structured. AddWithValue pasa el conjunto de resultados de OracleDataReader al procedimiento almacenado como parámetro con valores de tabla.The AddWithValue passes the OracleDataReader result set to the stored procedure as a table-valued parameter.

// Assumes connection is an open SqlConnection.  
// Retrieve data from Oracle.  
OracleCommand selectCommand = new OracleCommand(  
   "Select CategoryID, CategoryName FROM Categories;",  
   oracleConnection);  
OracleDataReader oracleReader = selectCommand.ExecuteReader(  
   CommandBehavior.CloseConnection);  
  
 // Configure the SqlCommand and table-valued parameter.  
 SqlCommand insertCommand = new SqlCommand(  
   "usp_InsertCategories", connection);  
 insertCommand.CommandType = CommandType.StoredProcedure;  
 SqlParameter tvpParam =  
    insertCommand.Parameters.AddWithValue(  
    "@tvpNewCategories", oracleReader);  
 tvpParam.SqlDbType = SqlDbType.Structured;  
  
 // Execute the command.  
 insertCommand.ExecuteNonQuery();  
' Assumes connection is an open SqlConnection.  
' Retrieve data from Oracle.  
Dim selectCommand As New OracleCommand( _  
  "Select CategoryID, CategoryName FROM Categories;", _  
  oracleConnection)  
Dim oracleReader As OracleDataReader = _  
  selectCommand.ExecuteReader(CommandBehavior.CloseConnection)  
  
' Configure SqlCommand and table-valued parameter.  
Dim insertCommand As New SqlCommand("usp_InsertCategories", connection)  
insertCommand.CommandType = CommandType.StoredProcedure  
Dim tvpParam As SqlParameter = _  
  insertCommand.Parameters.AddWithValue("@tvpNewCategories", _  
  oracleReader)  
tvpParam.SqlDbType = SqlDbType.Structured  
  
' Execute the command.  
insertCommand.ExecuteNonQuery()  

Vea tambiénSee also