Delen via

Parameters met tabelwaarde

  • Artikel

Parameters met tabelwaarden bieden een eenvoudige manier om meerdere rijen met gegevens van een clienttoepassing naar SQL Server te gebruiken zonder dat er meerdere retouren of speciale logica aan de serverzijde nodig zijn voor het verwerken van de gegevens. U kunt parameters met tabelwaarden gebruiken om rijen met gegevens in een clienttoepassing in te kapselen en de gegevens naar de server te verzenden in één geparameteriseerde opdracht. De binnenkomende gegevensrijen worden opgeslagen in een tabelvariabele die vervolgens kan worden uitgevoerd met behulp van Transact-SQL.

Kolomwaarden in parameters met tabelwaarden kunnen worden geopend met behulp van standaard Transact-SQL SELECT-instructies. Parameters met tabelwaarden worden sterk getypt en hun structuur wordt automatisch gevalideerd. De grootte van parameters met tabelwaarden wordt alleen beperkt door servergeheugen.

Notitie

U kunt geen gegevens retourneren in een tabelwaardeparameter. Parameters met tabelwaarden zijn alleen-invoer; het trefwoord OUTPUT wordt niet ondersteund.

Zie de volgende resources voor meer informatie over parameters met tabelwaarden.

Bron Beschrijving
Parameters met tabelwaarde gebruiken (database-engine) Hierin wordt beschreven hoe u parameters met tabelwaarden maakt en gebruikt.
Door de gebruiker gedefinieerde tabeltypen Beschrijft door de gebruiker gedefinieerde tabeltypen die worden gebruikt om parameters met tabelwaarden te declareren.

Meerdere rijen doorgeven in eerdere versies van SQL Server

Voordat parameters met tabelwaarden werden geïntroduceerd in SQL Server 2008, waren de opties voor het doorgeven van meerdere rijen met gegevens aan een opgeslagen procedure of een geparameteriseerde SQL-opdracht beperkt. Een ontwikkelaar kan kiezen uit de volgende opties voor het doorgeven van meerdere rijen aan de server:

  • Gebruik een reeks afzonderlijke parameters om de waarden in meerdere kolommen en rijen met gegevens weer te geven. De hoeveelheid gegevens die met deze methode kan worden doorgegeven, wordt beperkt door het aantal toegestane parameters. SQL Server-procedures kunnen maximaal 2100 parameters hebben. Logica aan de serverzijde is vereist om deze afzonderlijke waarden samen te stellen in een tabelvariabele of een tijdelijke tabel voor verwerking.

  • Bundel meerdere gegevenswaarden in gescheiden tekenreeksen of XML-documenten en geef deze tekstwaarden vervolgens door aan een procedure of instructie. Hiervoor moet de procedure of instructie de logica bevatten die nodig is voor het valideren van de gegevensstructuren en het loskoppelen van de waarden.

  • Maak een reeks afzonderlijke SQL-instructies voor gegevenswijzigingen die van invloed zijn op meerdere rijen, zoals de instructies die zijn gemaakt door de Update methode van een SqlDataAdapteraan te roepen. Wijzigingen kunnen afzonderlijk of in batches naar de server worden verzonden. Zelfs wanneer deze in batches worden ingediend die meerdere instructies bevatten, wordt elke instructie echter afzonderlijk uitgevoerd op de server.

  • Gebruik het bcp hulpprogramma of het SqlBulkCopy object om veel rijen met gegevens in een tabel te laden. Hoewel deze techniek zeer efficiënt is, biedt deze geen ondersteuning voor verwerking aan de serverzijde, tenzij de gegevens in een tijdelijke tabel of tabelvariabele worden geladen.

Parametertypen met tabelwaarde maken

Parameters met tabelwaarden zijn gebaseerd op sterk getypte tabelstructuren die worden gedefinieerd met behulp van Transact-SQL CREATE TYPE-instructies. U moet een tabeltype maken en de structuur definiëren in SQL Server voordat u parameters met tabelwaarden in uw clienttoepassingen kunt gebruiken. Zie Door de gebruiker gedefinieerde tabeltypen voor meer informatie over het maken van tabeltypen.

Met de volgende instructie maakt u een tabeltype met de naam CategoryTableType dat bestaat uit de kolommen CategoryID en CategoryName:

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

Nadat u een tabeltype hebt gemaakt, kunt u parameters met tabelwaarden declareren op basis van dat type. Het volgende Transact-SQL-fragment laat zien hoe u een tabelwaardeparameter declareert in een opgeslagen proceduredefinitie. Het sleutelwoord READONLY is vereist voor het declareren van een tabelwaardeparameter.

CREATE PROCEDURE usp_UpdateCategories
    (@tvpNewCategories dbo.CategoryTableType READONLY)

Gegevens wijzigen met parameters met tabelwaarde (Transact-SQL)

Parameters met tabelwaarden kunnen worden gebruikt in op set gebaseerde gegevenswijzigingen die van invloed zijn op meerdere rijen door één instructie uit te voeren. U kunt bijvoorbeeld alle rijen in een tabelwaardeparameter selecteren en deze invoegen in een databasetabel, of u kunt een update-instructie maken door een parameter met tabelwaarde toe te voegen aan de tabel die u wilt bijwerken.

De volgende Transact-SQL UPDATE-instructie laat zien hoe u een tabelwaardeparameter gebruikt door deze toe te voegen aan de tabel Categorieën. Wanneer u een tabelwaardeparameter gebruikt met een JOIN in een FROM-component, moet u deze ook aliasen, zoals hier wordt weergegeven, waarbij de parameter met tabelwaarde als 'ec' wordt aangeduid:

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

In dit Transact-SQL-voorbeeld ziet u hoe u rijen selecteert in een tabelwaardeparameter om een INSERT uit te voeren in één op set gebaseerde bewerking.

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

Beperkingen van parameters met tabelwaarde

Er zijn verschillende beperkingen voor parameters met tabelwaarden:

  • U kunt parameters met tabelwaarden niet doorgeven aan door de gebruiker gedefinieerde CLR-functies.

  • Parameters met tabelwaarden kunnen alleen worden geïndexeerd ter ondersteuning van beperkingen van UNIEKE of PRIMAIRE SLEUTELS. SQL Server onderhoudt geen statistieken voor parameters met tabelwaarden.

  • Parameters met tabelwaarden zijn alleen-lezen in Transact-SQL-code. U kunt de kolomwaarden in de rijen van een tabelwaardeparameter niet bijwerken en u kunt geen rijen invoegen of verwijderen. Als u de gegevens wilt wijzigen die worden doorgegeven aan een opgeslagen procedure of geparameteriseerde instructie in een tabelwaardeparameter, moet u de gegevens invoegen in een tijdelijke tabel of in een tabelvariabele.

  • U kunt ALTER TABLE-instructies niet gebruiken om het ontwerp van parameters met tabelwaarden te wijzigen.

Een SqlParameter-voorbeeld configureren

System.Data.SqlClientondersteunt het invullen van parameters met tabelwaarden van DataTableDbDataReader of IEnumerable<T> \ SqlDataRecord objecten. U moet een typenaam opgeven voor de parameter met tabelwaarde met behulp van de TypeName eigenschap van een SqlParameter. De TypeName naam moet overeenkomen met de naam van een compatibel type dat eerder op de server is gemaakt. Het volgende codefragment laat zien hoe u configureert SqlParameter om gegevens in te voegen.

In het volgende voorbeeld bevat de addedCategories variabele een DataTable. Als u wilt zien hoe de variabele wordt gevuld, raadpleegt u de voorbeelden in de volgende sectie, waarbij een parameter met tabelwaarde wordt doorgegeven aan een opgeslagen procedure.

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

U kunt ook elk object gebruiken dat is afgeleid van DbDataReader het streamen van rijen met gegevens naar een parameter met tabelwaarde, zoals wordt weergegeven in dit fragment:

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

Een parameter met tabelwaarde doorgeven aan een opgeslagen procedure

In dit voorbeeld ziet u hoe u parametergegevens met tabelwaarden doorgeeft aan een opgeslagen procedure. De code extraheert rijen in een nieuwe DataTable met behulp van de GetChanges methode. De code definieert vervolgens een SqlCommand, waarbij de CommandType eigenschap wordt ingesteld op StoredProcedure. De SqlParameter wordt gevuld met behulp van de AddWithValue methode en de SqlDbType is ingesteld op Structured. De SqlCommand methode wordt vervolgens uitgevoerd met behulp van de ExecuteNonQuery methode.

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

Een tabelwaardeparameter doorgeven aan een geparameteriseerde SQL-instructie

In het volgende voorbeeld ziet u hoe u gegevens in de dbo invoegt. Categorieëntabel met behulp van een INSERT-instructie met een SELECT-subquery met een parameter met tabelwaarde als gegevensbron. Wanneer u een parameter met tabelwaarde doorgeeft aan een geparameteriseerde SQL-instructie, moet u een typenaam opgeven voor de parameter met tabelwaarde met behulp van de nieuwe TypeName eigenschap van een SqlParameter. Dit TypeName moet overeenkomen met de naam van een compatibel type dat eerder op de server is gemaakt. De code in dit voorbeeld gebruikt de TypeName eigenschap om te verwijzen naar de typestructuur die is gedefinieerd in dbo. CategoryTableType.

Notitie

Als u een waarde opgeeft voor een identiteitskolom in een tabelwaardeparameter, moet u de instructie SET IDENTITY_INSERT voor de sessie uitgeven.

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

Rijen streamen met een DataReader

U kunt ook elk object gebruiken dat is afgeleid van DbDataReader het streamen van rijen met gegevens naar een parameter met tabelwaarde. Het volgende codefragment toont het ophalen van gegevens uit een Oracle-database met behulp van een OracleCommand en een OracleDataReader. De code configureert vervolgens een SqlCommand om een opgeslagen procedure aan te roepen met één invoerparameter. De SqlDbType eigenschap van de SqlParameter eigenschap is ingesteld op Structured. De AddWithValue resultatenset wordt doorgegeven OracleDataReader aan de opgeslagen procedure als een tabelwaardeparameter.

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

Zie ook