Tabellenwertparameter

Tabellenwertparameter bieten eine einfache Möglichkeit zum Marshallen mehrerer Datenzeilen aus einer Clientanwendung nach SQL Server, ohne dass mehrere Roundtrips oder eine spezielle serverseitige Logik für die Verarbeitung der Daten erforderlich sind. Sie können Tabellenwertparameter verwenden, um Datenzeilen in einer Clientanwendung zu kapseln und die Daten in einem einzigen parametrisierten Befehl an den Server zu senden. Die eingehenden Datenzeilen werden in einer Tabellenvariablen gespeichert, die Sie dann mithilfe von Transact-SQL bearbeiten können.

Auf Spaltenwerte in tabellenwertigen Parametern kann mithilfe von Standard-Transact-SQL-SELECTAnweisungen zugegriffen werden. Tabellenwertparameter sind stark typisiert, und ihre Struktur wird automatisch validiert. Die Größe von Tabellenwertparametern ist lediglich durch den Arbeitsspeicher des Servers beschränkt.

Hinweis

In Tabellenwertparametern können keine Daten zurückgegeben werden. Tabellenwertparameter sind reine Eingabeparameter. Das Schlüsselwort OUTPUT wird nicht unterstützt.

Weitere Informationen zu Tabellenwertparametern finden Sie in den folgenden Ressourcen.

Ressource	BESCHREIBUNG
Verwenden von Tabellenwertparameter (Datenbank-Engine)	Beschreibt, wie Sie Tabellenwertparameter erstellen und verwenden.
Benutzerdefinierte Tabellentypen	Beschreibt die benutzerdefinierten Tabellentypen, die zum Deklarieren von Tabellenwertparametern verwendet werden.

Übergeben von mehreren Zeilen in älteren Versionen von SQL Server

Vor der Einführung von Tabellenwertparametern in SQL Server 2008 bestanden nur eingeschränkte Möglichkeiten zum Übergeben mehrerer Datenzeilen an eine gespeicherte Prozedur oder einen parametrisierten SQL-Befehl. Entwickler hatten folgende Möglichkeiten, um mehrere Zeilen an den Server zu übergeben:

• Verwenden von mehreren Einzelparametern zur Darstellung der Werte in mehreren Datenspalten und -zeilen. Die Menge der Daten, die mit dieser Methode übergeben werden können, ist aufgrund der maximal zulässigen Anzahl von Parametern beschränkt. SQL Server-Prozeduren können höchstens über 2100 Parameter verfügen. Um diese Einzelwerte zur Verarbeitung in einer Tabellenvariable oder einer temporären Tabelle zusammenzusetzen, ist serverseitige Logik erforderlich.

• Bündeln mehrerer Datenwerte in Zeichenfolgen mit Trennzeichen oder XML-Dokumenten und Übergeben dieser Textwerte an eine Prozedur oder Anweisung. Bei dieser Vorgehensweise muss die Prozedur oder Anweisung die erforderliche Logik zum Überprüfen der Datenstrukturen und Entbündeln der Werte enthalten.

• Erstellen einer Reihe von SQL-Einzelanweisungen für Datenänderungen, die sich auf mehrere Zeilen auswirken, wie z. B. diejenigen, die mit der Update-Methode der SqlDataAdapter-Klasse erstellt wurden). Senden Sie Änderungen einzeln oder in Batches. Auch innerhalb eines Batches führt der Server jede Anweisung separat aus.

• Verwenden des bcp-Hilfsprogramms oder des SqlBulkCopy-Objekts, um eine Vielzahl von Datenzeilen in eine Tabelle zu laden. Diese Technik ist zwar sehr effizient, unterstützt jedoch keine serverseitige Verarbeitung, es sei denn, die Daten werden in eine temporäre Tabelle oder Tabellenvariable geladen.

Erstellen von Tabellenwertparameter-Typen

Tabellenwertparameter basieren auf stark typisierten Tabellenstrukturen, die mit CREATE TYPE-Anweisungen in Transact-SQL definiert werden. Sie müssen einen Tabellentyp erstellen und die Struktur in SQL Server definieren, bevor Sie Tabellenwertparameter in Ihren Clientanwendungen verwenden können. Weitere Informationen zum Erstellen von Tabellentypen finden Sie unter User-Defined Tabellentypen.

Mit der folgenden Anweisung wird ein Tabellentyp namens „CategoryTableType“ erstellt, der aus den Spalten „CategoryID“ und „CategoryName“ besteht:

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

Nachdem Sie einen Tabellentyp erstellt haben, können Sie Tabellenwertparameter basierend auf diesem Typ deklarieren. Das folgende Transact-SQL-Fragment veranschaulicht, wie ein Tabellenwertparameter in der Definition einer gespeicherten Prozedur deklariert wird. Beachten Sie, dass das READONLY Schlüsselwort zum Deklarieren eines Tabellenwertparameters erforderlich ist.

CREATE PROCEDURE usp_UpdateCategories
    (@tvpNewCategories dbo.CategoryTableType READONLY)

Ändern von Daten mit Tabellenwertparametern (Transact-SQL)

Um Tabellenwertparameter bei Datenänderungen zu verwenden, die auf Sätzen basieren und sich auf mehrere Zeilen auswirken, kann eine einzelne Anweisung ausgeführt werden. Beispielsweise können Sie alle Zeilen in einem Tabellenwertparameter auswählen und in eine Datenbanktabelle einfügen, oder Sie können eine update-Anweisung erstellen, indem Sie einen Tabellenwertparameter mit der Tabelle verbinden, die Sie aktualisieren möchten.

Die folgende Transact-SQL-Anweisung UPDATE veranschaulicht, wie ein Tabellenwertparameter verwendet wird, indem er der Tabelle "Categories" zugeordnet wird. Wenn Sie einen Tabellenwertparameter mit JOIN in einer FROM-Klausel verwenden, müssen Sie ihn auch aliasen, wie hier gezeigt, wobei der Parameter mit Tabellenwert als Alias „ec“ bezeichnet wird:

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

In diesem Transact-SQL Beispiel wird veranschaulicht, wie Zeilen aus einem Tabellenwertparameter ausgewählt werden, um einen INSERT in einem einzelnen satzbasierten Vorgang auszuführen.

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

Einschränkungen von Table-Valued Parametern

Für Tabellenwertparameter gelten verschiedene Einschränkungen:

• Sie können tabellenwertbezogene Parameter nicht an benutzerdefinierte CLR-Funktionen übergeben.

• Tabellenwertparameter können nur für die Unterstützung der UNIQUE- oder PRIMARY KEY-Einschränkungen indiziert werden. SQL Server verwaltet keine Statistiken zu Tabellenwertparametern.

• Tabellenwertparameter sind in Transact-SQL-Code nur lesbar. Sie können die Spaltenwerte in den Zeilen eines Tabellenwertparameters nicht aktualisieren, und Sie können keine Zeilen einfügen oder löschen. Um die Daten zu ändern, die an eine gespeicherte Prozedur oder eine parametrisierte Anweisung in einem Tabellenwertparameter übergeben werden, müssen Sie die Daten in eine temporäre Tabelle oder in eine Tabellenvariable einfügen.

• Sie können ALTER TABLE-Anweisungen nicht zur Änderung des Design von Tabellenwertparametern verwenden.

Konfigurieren eines SqlParameter-Beispiels

Installieren Sie das Microsoft.Data.SqlClient-NuGet-Paket und importieren Sie Microsoft.Data.SqlClient und System.Data. Für clientseitige Datensatzsequenzen verwenden Sie Microsoft.Data.SqlClient.Server.SqlDataRecord und Microsoft.Data.SqlClient.Server.SqlMetaData. Ersetzen Sie nicht die serverseitigen SQL CLR-Typen durch Microsoft.SqlServer.Server.

Microsoft.Data.SqlClient unterstützt Werte tabellenwertiger Parameter aus DataTable, DbDataReader oder IEnumerable<T> von SqlDataRecord-Objekten. Legen Sie den TypeName des Parameters auf den Namen eines kompatiblen Tabellentyps fest, der zuvor auf dem Server erstellt wurde. Das folgende Fragment konfiguriert den Parameter zum Einfügen von Daten.

Im folgenden Beispiel enthält die Variable addedCategories eine DataTable. Weitere Informationen zum Füllen der Variable finden Sie in den Beispielen im nächsten Abschnitt Übergeben eines Tabellenwertparameters an eine gespeicherte Prozedur.

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

Sie können auch jedes von DbDataReader abgeleitete Objekt verwenden, um Datenzeilen in einen tabellenwertigen Parameter zu streamen, wie im folgenden Fragment dargestellt.

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

Übergeben eines Table-Valued Parameters an eine gespeicherte Prozedur

In diesem Beispiel werden Tabellenwertparameterdaten an eine gespeicherte Prozedur übergeben. Der Code ruft GetChanges auf, um hinzugefügte Zeilen in das neue DataTablezu extrahieren. Es erstellt den SqlCommand mit CommandType auf StoredProcedure gesetzt. Er fügt den Parameter mit AddWithValue hinzu und setzt SqlDbType auf Structured. Schließlich ruft der Code ExecuteNonQuery auf.

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

Übergeben eines Table-Valued Parameters an eine parametrisierte SQL-Anweisung

Im folgenden Beispiel werden Daten dbo.Categories mit einer INSERT Anweisung und einer SELECT Unterabfrage eingefügt, die den tabellenwertigen Parameter liest. Legen Sie für eine parametrisierte SQL-Anweisung TypeName auf den Namen eines kompatiblen Tabellentyps fest, der zuvor auf dem Server erstellt wurde. In diesem Beispiel wird dbo.CategoryTableType verwendet.

Hinweis

Wenn Sie einen Wert für eine Identitätsspalte in einem Tabellenwertparameter angeben, müssen Sie die SET IDENTITY_INSERT Anweisung für die Sitzung ausgeben.

// 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 von Zeilen mit einem DataReader

Sie können jedes von DbDataReader abgeleitete Objekt verwenden, um Zeilen per Streaming in einen Tabellenwertparameter zu übertragen. Das folgende Fragment ruft Daten aus einer Oracle-Datenbank mit OracleCommand und OracleDataReader. Der Code konfiguriert das SqlCommand so, dass es eine gespeicherte Prozedur mit einem Eingabeparameter aufruft. Er legt SqlDbType auf Structured fest und übergibt das OracleDataReader-Resultset über AddWithValue als Tabellenwertparameter.

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

Siehe auch

• Konfigurieren von Parametern und Parameterdatentypen
• Befehle und Parameter
• DataAdapter-Parameter
• SQL Server-Datenvorgänge in ADO.NET
• Übersicht über ADO.NET