Parámetros de Table-Valued

Los parámetros con valores de tabla proporcionan una manera sencilla de transferir varias filas de datos desde una aplicación cliente a SQL Server sin necesidad de realizar múltiples consultas ni de ninguna lógica especial en el servidor para procesar los datos. Puede usar parámetros con valores de tabla para encapsular filas de datos en una aplicación cliente y enviar los datos al servidor en un único comando con parámetros. Las filas de datos entrantes se almacenan en una variable de tabla en la que, a continuación, se puede operar mediante el uso de Transact-SQL.

Se puede tener acceso a los valores de columna de los parámetros con valores de tabla mediante instrucciones Transact-SQL SELECT estándar. Los parámetros de tipo tabla están fuertemente tipados y su estructura se valida automáticamente. El tamaño de los parámetros con valores de tabla solo está limitado por la memoria del servidor.

Para obtener más información sobre los parámetros con valores de tabla, vea los siguientes recursos.

Pasar varias filas de versiones previas de SQL Server

Antes de la incorporación de los parámetros con valores de tabla a 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. Un desarrollador podría elegir entre las siguientes opciones para pasar varias filas al servidor:

• Usar una serie de parámetros individuales para representar los valores de varias columnas y filas de datos. La cantidad de datos que se pueden pasar mediante este método está limitada por el número de parámetros permitidos. Los procedimientos de SQL Server pueden tener 2100 parámetros como máximo. La lógica del lado servidor es necesaria para ensamblar estos valores individuales en una variable de tabla o una tabla temporal para su procesamiento.

• Agrupar 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. Esto requiere que el procedimiento o la instrucción incluyan la lógica necesaria para validar las estructuras de datos y desagrupar los valores.

• Crea una serie de instrucciones SQL individuales para las modificaciones de datos que afectan a varias filas, como las generadas por el método Update de SqlDataAdapter. Enviar cambios individualmente o en lotes. Incluso dentro de un lote, el servidor ejecuta cada instrucción por separado.

• Use el programa de la utilidad bcp o el objeto SqlBulkCopy para cargar muchas filas de datos en una tabla. Aunque esta técnica es muy eficaz, no admite el procesamiento del lado servidor a menos que los datos se carguen en una tabla o variable de tabla temporal.

Creación de tipos de parámetros para Table-Valued

Los parámetros con valores de tabla se basan en estructuras de tabla fuertemente tipadas definidas mediante instrucciones CREATE TYPE de Transact-SQL. Debe crear un tipo de tabla y definir la estructura en SQL Server para poder usar los parámetros con valores de tabla en las aplicaciones de cliente. Para obtener más información sobre cómo crear tipos de tabla, consulte User-Defined Tipos de tabla.

La instrucción siguiente crea un tipo de tabla denominado CategoryTableType que consta de las columnas CategoryID y CategoryName:

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

Después de crear un tipo de tabla, puede declarar parámetros con valores de tabla basados en ese tipo. El siguiente fragmento de Transact-SQL muestra cómo declarar un parámetro con valores de tabla en una definición de procedimiento almacenado. Tenga en cuenta que la READONLY palabra clave es necesaria para declarar un parámetro con valores de tabla.

CREATE PROCEDURE usp_UpdateCategories
    (@tvpNewCategories dbo.CategoryTableType READONLY)

Modificar datos con parámetros de Table-Valued (Transact-SQL)

Los parámetros con valores de tabla se pueden utilizar en las modificaciones de datos basadas en conjuntos que afectan a varias filas mediante la ejecución de una única instrucción. 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 puede crear una instrucción UPDATE combinando un parámetro con valores de tabla en la tabla que desea actualizar.

La siguiente instrucción Transact-SQL UPDATE muestra cómo usar un parámetro con valores de tabla uniéndose a la tabla Categories. Cuando utilices un parámetro con valor de tabla junto con un JOIN en una cláusula FROM, también debes asignarle un alias, tal y como se muestra aquí, donde al parámetro con valor de tabla se le ha asignado el alias 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 filas de un parámetro de tabla para realizar una operación INSERT en una sola operación basada en conjuntos.

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

Limitaciones de los parámetros de Table-Valued

Existen varias limitaciones para los parámetros con valores de tabla:

• No se pueden pasar parámetros con valores de tabla a funciones definidas por el usuario clR.

• Los parámetros con valores de tabla solo se pueden indexar para admitir restricciones UNIQUE o PRIMARY KEY. SQL Server no mantiene estadísticas en parámetros con valores de tabla.

• Los parámetros con valores de tabla son de solo lectura en el código de Transact-SQL. No se pueden actualizar los valores de columna en las filas de un parámetro con valores de tabla y no se pueden insertar ni eliminar filas. Para modificar los datos que se pasan a un procedimiento almacenado o a una instrucción con parámetros en el parámetro con valores de tabla, debe insertar los datos en una tabla temporal o en una variable de tabla.

• No puedes utilizar instrucciones ALTER TABLE para modificar el diseño de los parámetros con valor de tabla.

Configuración de un ejemplo de SqlParameter

Instale el paquete NuGet Microsoft.Data.SqlClient e importe Microsoft.Data.SqlClient y System.Data. Para las secuencias de registros del lado cliente, use Microsoft.Data.SqlClient.Server.SqlDataRecord y Microsoft.Data.SqlClient.Server.SqlMetaData. No sustituya los tipos del lado servidor CLR de SQL de Microsoft.SqlServer.Server.

Microsoft.Data.SqlClient admite valores de parámetros con valores de tabla procedentes de DataTable, DbDataReader o IEnumerable<T> de los objetos SqlDataRecord. Establezca el TypeName del parámetro en el nombre de un tipo de tabla compatible creado anteriormente en el servidor. El fragmento siguiente configura el parámetro para insertar datos.

En el siguiente ejemplo, la variable addedCategories contiene DataTable. Para ver cómo se rellena la variable, vea los ejemplos de la sección siguiente Paso de un parámetro con valores de tabla a un procedimiento almacenado.

// 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"

También puede usar cualquier objeto derivado de DbDataReader para transmitir filas de datos a un parámetro con valores de tabla, como se muestra en este fragmento:

// 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 Table-Valued a un procedimiento almacenado

En este ejemplo se pasan datos de un parámetro con valores de tabla a un procedimiento almacenado. El código llama a GetChanges para extraer las filas añadidas a la nueva DataTable. Crea el objeto SqlCommand con StoredProcedure establecido en CommandType. Agrega el parámetro con AddWithValue y establece SqlDbType en Structured. Por último, el código llama a ExecuteNonQuery.

// 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 de Table-Valued a una instrucción SQL con parámetros

En el ejemplo siguiente se insertan datos en dbo.Categories con una INSERT instrucción y una SELECT subconsulta que lee el parámetro de valores de tabla. Para una instrucción SQL con parámetros, asigne a TypeName el nombre de un tipo de tabla compatible creado anteriormente en el servidor. En este ejemplo se usa dbo.CategoryTableType.

// 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

Transmisión de filas con el lector de datos

Puede usar cualquier objeto derivado de DbDataReader para transmitir filas a un parámetro con valores de tabla. El fragmento siguiente recupera datos de una base de datos de Oracle con OracleCommand y OracleDataReader. El código configura SqlCommand para invocar un procedimiento almacenado con un parámetro de entrada. Establece SqlDbType en Structured y pasa el conjunto de resultados OracleDataReader a través de AddWithValue como parámetro con valores de tabla.

// 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()

Consulte también

• Configuración de parámetros y tipos de datos de parámetros
• Comandos y parámetros
• Parámetros de DataAdapter
• Operaciones de datos de SQL Server en ADO.NET
• Información general de ADO.NET