Table-Valued Parâmetros

Os parâmetros com valor de tabela fornecem uma maneira fácil de empacotar várias linhas de dados de um aplicativo cliente para o SQL Server sem exigir várias viagens de ida e volta ou lógica especial do lado do servidor para processar os dados. Você pode usar parâmetros com valor de tabela para encapsular linhas de dados em um aplicativo cliente e enviar os dados para o servidor em um único comando parametrizado. As linhas de dados de entrada são armazenadas em uma variável de tabela que pode ser operada usando Transact-SQL.

Os valores de coluna em parâmetros com valor de tabela podem ser acessados usando instruções padrão Transact-SQL SELECT. Os parâmetros com valor de tabela são fortemente tipados e a sua estrutura é validada automaticamente. O tamanho dos parâmetros com valor de tabela é limitado apenas pela memória do servidor.

Para obter mais informações sobre parâmetros com valor de tabela, consulte os recursos a seguir.

Transmissão de várias linhas em versões anteriores do SQL Server

Antes dos parâmetros com valor de tabela serem introduzidos no SQL Server 2008, as opções para passar várias linhas de dados para um procedimento armazenado ou um comando SQL parametrizado eram limitadas. Um desenvolvedor pode escolher entre as seguintes opções para passar várias linhas para o servidor:

• Use uma série de parâmetros individuais para representar os valores em várias colunas e linhas de dados. A quantidade de dados que podem ser passados usando esse método é limitada pelo número de parâmetros permitidos. Os procedimentos do SQL Server podem ter, no máximo, 2100 parâmetros. A lógica do lado do servidor é necessária para montar esses valores individuais em uma variável de tabela ou uma tabela temporária para processamento.

• Agrupe vários valores de dados em cadeias de caracteres delimitadas ou documentos XML e, em seguida, passe esses valores de texto para um procedimento ou instrução. Isto requer que o procedimento ou instrução inclua a lógica necessária para validar as estruturas de dados e desagregar os valores.

• Crie uma série de instruções SQL separadas para modificações de dados que afetam várias linhas, como aquelas criadas ao chamar o método Update de um SqlDataAdapter. As alterações podem ser enviadas ao servidor individualmente ou em lotes em grupos. No entanto, mesmo quando enviado em lotes que contêm várias instruções, cada instrução é executada separadamente no servidor.

• Use o bcp programa utilitário ou o SqlBulkCopy objeto para carregar muitas linhas de dados em uma tabela. Embora essa técnica seja muito eficiente, ela não suporta processamento do lado do servidor, a menos que os dados sejam carregados em uma tabela temporária ou variável de tabela.

Criando tipos de parâmetros Table-Valued

Os parâmetros com valor de tabela são baseados em estruturas de tabela fortemente tipificadas que são definidas usando as instruções Transact-SQL CREATE TYPE. Você precisa criar um tipo de tabela e definir a estrutura no SQL Server antes de poder usar parâmetros com valor de tabela em seus aplicativos cliente. Para obter mais informações sobre como criar tipos de tabela, consulte User-Defined Tipos de tabela.

A instrução a seguir cria um tipo de tabela chamado CategoryTableType que consiste nas colunas CategoryID e CategoryName:

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

Depois de criar um tipo de tabela, você pode declarar parâmetros com valor de tabela com base nesse tipo. O fragmento de Transact-SQL a seguir demonstra como declarar um parâmetro com valor de tabela em uma definição de procedimento armazenado. Observe que a palavra-chave READONLY é necessária para declarar um parâmetro com valor de tabela.

CREATE PROCEDURE usp_UpdateCategories
    (@tvpNewCategories dbo.CategoryTableType READONLY)  

Modificando dados com parâmetros Table-Valued (Transact-SQL)

Os parâmetros com valor de tabela podem ser usados em modificações de dados baseadas em conjunto que afetam várias linhas executando uma única instrução. Por exemplo, você pode selecionar todas as linhas em um parâmetro com valor de tabela e inseri-las em uma tabela de banco de dados ou pode criar uma instrução update unindo um parâmetro com valor de tabela à tabela que deseja atualizar.

A instrução UPDATE Transact-SQL a seguir demonstra como usar um parâmetro com valor de tabela juntando-o à tabela Categories. Quando utiliza um parâmetro com valor de tabela com um JOIN numa cláusula FROM, deve também dar-lhe um alias, como mostrado aqui, onde o parâmetro com valor de tabela é denominado como "ec":

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

Este exemplo Transact-SQL demonstra como selecionar linhas de um parâmetro com valor de tabela para realizar um INSERT em uma única operação de conjunto.

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

Limitações dos parâmetros Table-Valued

Há várias limitações para parâmetros com valor de tabela:

• Não é possível passar parâmetros com valor de tabela para funções definidas pelo usuário CLR.

• Os parâmetros com valor de tabela só podem ser indexados para suportar restrições de CHAVE EXCLUSIVA ou PRIMÁRIA. O SQL Server não mantém estatísticas sobre parâmetros com valor de tabela.

• Os parâmetros valorizados em tabela são de leitura única no código Transact-SQL. Não é possível atualizar os valores de coluna nas linhas de um parâmetro com valor de tabela e não é possível inserir ou excluir linhas. Para modificar os dados que são passados para um procedimento armazenado ou instrução parametrizada no parâmetro com valor de tabela, você deve inserir os dados em uma tabela temporária ou em uma variável de tabela.

• Não é possível usar instruções ALTER TABLE para modificar o design de parâmetros com valor de tabela.

Configurando um exemplo de SqlParameter

System.Data.SqlClient suporta o preenchimento de parâmetros de valor de tabela a partir de DataTable, DbDataReader ou objetos IEnumerable<T> \ SqlDataRecord. Você deve especificar um nome de tipo para o parâmetro com valor de tabela usando a propriedade TypeName de um SqlParameter. O TypeName deve corresponder ao nome de um tipo compatível criado anteriormente no servidor. O fragmento de código a seguir demonstra como configurar SqlParameter para inserir dados.

No exemplo a seguir, a variável addedCategories contém um DataTable. Para ver como a variável é preenchida, consulte os exemplos na próxima seção, Passando um parâmetro de Table-Valued para um procedimento armazenado.

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

Você também pode usar qualquer objeto derivado de DbDataReader para transmitir linhas de dados para um parâmetro com valor de tabela, conforme mostrado neste 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  

Passando um parâmetro Table-Valued para um procedimento armazenado

Este exemplo demonstra como passar dados de parâmetros com valor de tabela para um procedimento armazenado. O código extrai linhas adicionadas em uma nova DataTable usando o método GetChanges. Em seguida, o código define um SqlCommand, definindo a CommandType propriedade como StoredProcedure. O SqlParameter é preenchido usando o AddWithValue método e o SqlDbType é definido como Structured. O SqlCommand é então executado usando o ExecuteNonQuery método.

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

Passando um parâmetro Table-Valued para uma instrução SQL parametrizada

O exemplo a seguir demonstra como inserir dados na tabela dbo.Categories usando uma instrução INSERT com uma subconsulta SELECT que tem um parâmetro do tipo de tabela como fonte de dados. Ao passar um parâmetro com valor de tabela para uma instrução SQL parametrizada, você deve especificar um nome de tipo para o parâmetro com valor de tabela usando a nova TypeName propriedade de um SqlParameter. Este TypeName deve corresponder ao nome de um tipo de compatibilidade criado previamente no servidor. O código neste exemplo usa a TypeName propriedade para fazer referência à estrutura de tipo definida em 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  

Transmissão de linhas com um DataReader

Você também pode usar qualquer objeto derivado de DbDataReader para transmitir linhas de dados para um parâmetro com valor de tabela. O fragmento de código a seguir demonstra a recuperação de dados de um banco de dados Oracle usando um OracleCommand e um OracleDataReader. Em seguida, o código configura um SqlCommand para invocar um procedimento armazenado com um único parâmetro de entrada. A SqlDbType propriedade do SqlParameter é definida como Structured. O AddWithValue passa o OracleDataReader conjunto de resultados para o procedimento armazenado como um parâmetro com valor de tabela.

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

Ver também

• Configurando parâmetros e tipos de dados de parâmetros
• Comandos e parâmetros
• Parâmetros DataAdapter
• Operações de dados do SQL Server no ADO.NET
• Visão geral ADO.NET