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 das colunas em parâmetros de tabela podem ser acedidos usando instruções Transact-SQL SELECT padrão. 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.

Observação

Não é possível retornar dados em um parâmetro com valor de tabela. Os parâmetros com valores de tabela são apenas de entrada; a OUTPUT palavra-chave não é suportada.

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

Recurso	Descrição
Usar parâmetros do tipo tabela (Motor de Base de Dados)	Descreve como criar e usar parâmetros com valor de tabela.
User-Defined tipos de tabela	Descreve os tipos de tabela definidos pelo usuário que são usados para declarar parâmetros com valor de tabela.

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 individuais para modificações de dados que afetem múltiplas linhas, como as criadas pelo método Update do SqlDataAdapter. Submeta alterações individualmente ou em lotes. Mesmo dentro de um lote, o servidor executa cada instrução separadamente.
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 de tabela baseiam-se em estruturas de tabela fortemente tipadas que são definidas utilizando declaraçõ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. Note que a READONLY palavra-chave é 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 seguinte afirmação Transact-SQL UPDATE demonstra como usar um parâmetro com valores de tabela ao ligá-lo à tabela Categorias. Quando usa um parâmetro com valores de tabela com a JOIN numa cláusula FROM, deve também atribuir-lhe um alias, como mostrado aqui, onde o parâmetro com valores 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 de Transact-SQL demonstra como selecionar linhas de um parâmetro de valor de tabela para realizar uma operação de INSERT numa única operação baseada em conjuntos.

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.
Parâmetros com valores de tabela só podem ser indexados para suportar restrições UNIQUE ou PRIMARY KEY. 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 pode usar ALTER TABLE declarações para modificar a estrutura de parâmetros com valores de tabela.

Configurando um exemplo de SqlParameter

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

Microsoft.Data.SqlClient suporta valores de parâmetros com valor de tabela provenientes de DataTable, DbDataReader ou IEnumerable<T> de objetos SqlDataRecord. Defina o TypeName do parâmetro como o nome de um tipo de tabela compatível previamente criado no servidor. O fragmento seguinte configura o parâmetro 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 passa dados de parâmetros com valor de tabela para um procedimento armazenado. O código chama GetChanges para extrair as linhas adicionadas para o novo DataTable. Cria o SqlCommand com CommandType definido para StoredProcedure. Adiciona o parâmetro com AddWithValue e define SqlDbType para Structured. Finalmente, 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 seguinte insere dados em dbo.Categories com uma INSERT instrução e uma SELECT subconsulta que lê o parâmetro com valores de tabela. Para uma instrução SQL parametrizada, defina TypeName como o nome de um tipo de tabela compatível previamente criado no servidor. Este exemplo utiliza dbo.CategoryTableType.

Observação

Se fornecer um valor para uma coluna de identidade num parâmetro com valor de tabela, tem de executar a instrução SET IDENTITY_INSERT para essa 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

Transmissão de linhas com um DataReader

Pode usar qualquer objeto derivado de DbDataReader para direcionar linhas para um parâmetro com valor de tabela. O fragmento seguinte recupera dados de uma base 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 através de AddWithValue como 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

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