Parameters met tabelwaarden

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 benaderd met behulp van standaard-Transact-SQL-instructies SELECT. Parameters met tabelwaarden worden sterk getypt en hun structuur wordt automatisch gevalideerd. De grootte van parameters met tabelwaarden wordt alleen beperkt door servergeheugen.

Opmerking

U kunt geen gegevens retourneren in een tabelwaardeparameter. Tabelwaardeparameters kunnen alleen als invoer worden gebruikt; het OUTPUT trefwoord wordt niet ondersteund.

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

Hulpbron	Beschrijving
Table-Valued Parameters (Database Engine) gebruiken	Hierin wordt beschreven hoe u parameters met tabelwaarden maakt en gebruikt.
User-Defined 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 die zijn gemaakt met de Update methode van de SqlDataAdapter. Dien wijzigingen afzonderlijk of in groepen in. Zelfs binnen een batch voert de server elke instructie afzonderlijk uit.
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 voor Table-Valued 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 User-Defined 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. In het volgende Transact-SQL fragment ziet u hoe u een tabelwaardeparameter declareert in een opgeslagen proceduredefinitie. Houd er rekening mee dat het trefwoord READONLYREADONLYREADONLY vereist is voor het declareren van een parameter met tabelwaarde.

CREATE PROCEDURE usp_UpdateCategories
    (@tvpNewCategories dbo.CategoryTableType READONLY)

Gegevens wijzigen met Table-Valued Parameters (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.

In de volgende Transact-SQL-instructie UPDATE ziet u hoe u een parameter met tabelwaarden gebruikt door deze toe te voegen aan de tabel Categorieën. Wanneer u een tabelwaardeparameter gebruikt met een JOIN in een FROM clausule, moet u deze ook een alias geven, zoals hier wordt weergegeven, waarbij de parameter met tabelwaarde als 'ec' wordt gealiast:

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 in één set gebaseerde bewerking uit te voeren.

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

Beperkingen van Table-Valued parameters

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 UNIQUE- of PRIMARY KEY-beperkingen. SQL Server onderhoudt geen statistieken voor parameters met tabelwaarden.
Parameterwaarden van tabellen zijn alleen-lezen in de 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

Installeer het Microsoft.Data.SqlClient NuGet-pakket en importeer Microsoft.Data.SqlClient en System.Data. Gebruik voor recordreeksen aan de clientzijde Microsoft.Data.SqlClient.Server.SqlDataRecord en Microsoft.Data.SqlClient.Server.SqlMetaData. Vervang de SQL CLR-servertypen van Microsoft.SqlServer.Server niet.

Microsoft.Data.SqlClient ondersteunt tabelwaarde-parameterwaarden van DataTable, DbDataReader, of IEnumerable<T> van SqlDataRecord-objecten. Stel de TypeName van de parameter in op de naam van een compatibel tabeltype dat eerder op de server is gemaakt. Het volgende fragment configureert de parameter voor het invoegen van gegevens.

In het volgende voorbeeld bevat de addedCategories variabele een DataTable. Zie de voorbeelden in de volgende sectie, een Table-Valued parameter doorgeven aan een opgeslagen procedure om te zien hoe de variabele wordt ingevuld.

// 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 Table-Valued-parameter doorgeven aan een opgeslagen procedure

In dit voorbeeld worden gegevens van parameters met tabelwaarde doorgegeven aan een stored procedure. De code roept GetChanges aan om toegevoegde rijen naar het nieuwe DataTable te extraheren. Hiermee wordt de SqlCommand gemaakt, waarbij CommandType is ingesteld op StoredProcedure. De parameter wordt toegevoegd met AddWithValue en wordt SqlDbType ingesteld op Structured. Ten slotte roept de code ExecuteNonQuery aan.

// 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 Table-Valued parameter doorgeven aan een geparameteriseerde SQL-instructie

In het volgende voorbeeld worden gegevens in dbo.Categories ingevoegd met een INSERT instructie en een SELECT subquery die de parameter met tabelwaarde leest. Stel voor een geparameteriseerde SQL-instructie TypeName in op de naam van een compatibel tabeltype dat eerder op de server is gemaakt. In dit voorbeeld wordt dbo.CategoryTableTypegebruikt.

Opmerking

Als u een waarde opgeeft voor een identiteitskolom in een tabelwaardeparameter, moet u de SET IDENTITY_INSERT instructie 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 elk object dat is afgeleid van DbDataReader gebruiken om rijen te streamen naar een parameter met tabelwaarde. Met het volgende fragment worden gegevens opgehaald uit een Oracle-database met OracleCommand en OracleDataReader. De code configureert de SqlCommand om een opgeslagen procedure aan te roepen met één invoerparameter. Hiermee stelt u OracleDataReader in op AddWithValue en geeft u de resultatenset SqlDbType door via Structured als de parameter met tabelwaarde.

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

Laatst bijgewerkt om 2026-10-27