Parâmetros com valor de tabela

Os parâmetros com valor de tabela fornecem uma maneira fácil de realizar o marshaling de várias linhas de dados de um aplicativo cliente para o SQL Server sem exigir múltiplas idas e voltas 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, as quais você poderá operar usando o Transact-SQL.

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

Observação

Não é possível retornar dados em um parâmetro com valor de tabela. Os parâmetros com valor de tabela são somente entrada; não há suporte para a palavra-chave OUTPUT.

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

Recurso	Descrição
Usar parâmetros com valor de tabela (Mecanismo de Banco de Dados)	Descreve como criar e usar parâmetros com valor de tabela.
Tipos de tabela definidos pelo usuário	Descreve os tipos de tabela definidos pelo usuário usados para declarar parâmetros com valor de tabela.

Passando várias linhas em versões anteriores do SQL Server

Antes de os parâmetros de valores 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:

Usar uma série de parâmetros individuais para representar os valores em várias colunas e linhas de dados. A quantidade de dados que pode ser passada 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 múltiplos valores de dados em cadeias de caracteres delimitadas ou em documentos XML e, em seguida, passe esses valores de texto para um procedimento ou uma instrução. Isso requer que o procedimento ou a instrução inclua a lógica necessária para validar as estruturas de dados e desagrupar os valores.
Crie uma série de instruções SQL individuais para modificações de dados que afetam várias linhas, como aquelas criadas pelo método Update do SqlDataAdapter. Envie alterações individualmente ou em lotes. Mesmo em um lote, o servidor executa cada instrução separadamente.
Usar o programa utilitário bcp ou o objeto SqlBulkCopy para carregar várias linhas de dados em uma tabela. Embora essa técnica seja muito eficiente, ela não dá suporte ao 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âmetro com valor de tabela

Os parâmetros com valor de tabela se baseiam em estruturas de tabela fortemente tipadas que são definidas usando 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 de valores de tabela em seus aplicativos cliente. Para obter mais informações sobre como criar tipos de tabela, consulte User-Defined Tipos de Tabela.

A seguinte instrução cria um tipo de tabela chamado CategoryTableType que é composto pelas colunas CategoryID e CategoryName:

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

Depois de criar um tipo de tabela é possível declarar os parâmetros com valor de tabela baseados nesse tipo. O fragmento do 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 de Table-Valued (Transact-SQL)

Os parâmetros com valor de tabela podem ser usados em modificações de dados baseadas em conjuntos que afetam várias linhas com a execução de 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 declaração de atualização unindo um parâmetro com valor de tabela à tabela que você deseja atualizar.

A instrução Transact-SQL UPDATE a seguir demonstra como usar um parâmetro com valor de tabela, unindo-o à tabela Categorias. Ao usar um parâmetro com valor de tabela com um JOIN em uma cláusula FROM, você também deve atribuir um alias a ele, conforme mostrado aqui, em que o parâmetro com valor de tabela recebe o 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 exemplo do Transact-SQL demonstra como selecionar linhas de um parâmetro com valor de tabela para executar um INSERT em uma operação baseada em conjunto.

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

Limitações de parâmetros de 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.
Parâmetros com valor de tabela só podem ser indexados para dar suporte a restrições UNIQUE ou PRIMARY KEY. O SQL Server não mantém estatísticas em parâmetros com valor de tabela.
Os parâmetros com valor de tabela são somente leitura no código Transact-SQL. Você não pode atualizar os valores de coluna nas linhas de um parâmetro com valor de tabela e não pode inserir ou excluir linhas. Para modificar os dados que são passados para um procedimento armazenado ou para uma instrução parametrizada no parâmetro com valor de tabela, será necessário 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 dos parâmetros com valor de tabela.

Configurando um exemplo de SqlParameter

Instale o pacote NuGet Microsoft.Data.SqlClient e importe System.Data e Microsoft.Data.SqlClient. Para sequências de registro do lado do cliente, use Microsoft.Data.SqlClient.Server.SqlDataRecord e Microsoft.Data.SqlClient.Server.SqlMetaData. Não substitua os tipos do lado do servidor SQL CLR de Microsoft.SqlServer.Server.

Microsoft.Data.SqlClient permite valores de parâmetros com valor de tabela de DataTable, DbDataReader ou IEnumerable<T> dos objetos SqlDataRecord. Defina o TypeName do parâmetro como o nome de um tipo de tabela compatível criado anteriormente no servidor. O fragmento a seguir configura o parâmetro para inserir dados.

No exemplo a seguir, o nome da variável addedCategories contém um DataTable. Para ver como a variável é preenchida, confira os exemplos na próxima seção, Como passar um parâmetro com valor de tabela 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"

Também é possível usar qualquer objeto derivado de DbDataReader para transmitir linhas de dados a 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 transmite dados de um parâmetro com valor de tabela para um procedimento armazenado. O código chama GetChanges para extrair as linhas adicionadas para o novo DataTable. Ele cria o SqlCommand com CommandType definido como StoredProcedure. Ele adiciona o parâmetro com AddWithValue e define SqlDbType como Structured. Por fim, o código chama 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

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

O exemplo a seguir insere dados em dbo.Categories com uma instrução INSERT e uma subconsulta SELECT que lê o parâmetro com valor de tabela. Para uma instrução SQL parametrizada, defina TypeName como o nome de um tipo de tabela compatível criado anteriormente no servidor. Este exemplo usa dbo.CategoryTableType.

Observação

Se você fornecer um valor para uma coluna de identidade em um parâmetro tabular, deverá executar a instrução SET IDENTITY_INSERT na sessão.

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

Transmitindo linhas com um DataReader

É possível usar qualquer objeto derivado de DbDataReader para transmitir linhas para um parâmetro com valor de tabela. O fragmento a seguir recupera dados de um banco de dados Oracle com OracleCommand e OracleDataReader. O código configura o SqlCommand para invocar um procedimento armazenado com um parâmetro de entrada. Define SqlDbType como Structured e passa o conjunto de resultados OracleDataReader por meio de AddWithValue como o 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()

Consulte também

Última atualização em 2026-10-27