Partilhar via

Parâmetros com valor de tabela

  • Artigo

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 sua estrutura é validada automaticamente. O tamanho dos parâmetros com valor de tabela é limitado apenas pela memória do servidor.

Nota

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

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

Recurso Description
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 que são usados para declarar parâmetros com valor de tabela.

Passando 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 afetam várias linhas, como aquelas criadas chamando o Update método de um SqlDataAdapterarquivo . 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 com valor de tabela

Os parâmetros com valor de tabela são baseados 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 com valor de tabela em seus aplicativos cliente. Para obter mais informações sobre como criar tipos de tabela, consulte Tipos de tabela definidos pelo usuário.

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 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 com valor de tabela (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 Transact-SQL UPDATE a seguir demonstra como usar um parâmetro com valor de tabela associando-o à tabela Categories. Quando você usa um parâmetro com valor de tabela com um JOIN em uma cláusula FROM, você também deve usá-lo, como mostrado aqui, onde o parâmetro com valor de tabela é aliased 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 executar um INSERT em uma única operação baseada em conjunto.

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

Limitações dos parâmetros com valor de tabela

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 com valor de tabela são somente leitura 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 com valor de tabela de DataTable, DbDataReader ou IEnumerable<T> \ SqlDataRecord objetos. Você deve especificar um nome de tipo para o parâmetro com valor de tabela usando a TypeName propriedade de um SqlParameterarquivo . 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 addedCategories variável contém um DataTablearquivo . Para ver como a variável é preenchida, consulte os exemplos na próxima seção, Passando 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"

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 com valor de tabela 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 um novo DataTable usando o GetChanges método. 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 com valor de tabela para uma instrução SQL parametrizada

O exemplo a seguir demonstra como inserir dados no dbo. Tabela de categorias usando uma instrução INSERT com uma subconsulta SELECT que tem um parâmetro com valor 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. Isso TypeName deve corresponder ao nome de um tipo compatível criado anteriormente no servidor. O código neste exemplo usa a TypeName propriedade para fazer referência à estrutura de tipo definida em dbo. CategoryTableType.

Nota

Se você fornecer um valor para uma coluna de identidade em um parâmetro com valor de tabela, deverá emitir a instrução SET IDENTITY_INSERT para a 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

Streaming 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 conjunto de OracleDataReader 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()

Consulte também