Hinweis
Für den Zugriff auf diese Seite ist eine Autorisierung erforderlich. Sie können versuchen, sich anzumelden oder das Verzeichnis zu wechseln.
Für den Zugriff auf diese Seite ist eine Autorisierung erforderlich. Sie können versuchen, das Verzeichnis zu wechseln.
Tabellenwertparameter bieten eine einfache Möglichkeit zum Marshallen mehrerer Datenzeilen aus einer Clientanwendung in SQL Server. Für die Verarbeitung der Daten sind nicht mehrere Roundtrips und keine spezielle serverseitige Logik erforderlich. 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.
Für den Zugriff auf Spaltenwerte in Tabellenwertparametern können Standard-SELECT-Anweisungen in Transact-SQL verwendet 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. |
| Erstellen eines benutzerdefinierten Tabellentyps | Beschreibt die benutzerdefinierten Tabellentypen, die zum Deklarieren von Tabellenwertparametern verwendet werden. |
| Benutzerdefinierte Tabellentypen | Beschreibt die benutzerdefinierten Tabellentypen, die zum Deklarieren von Tabellenwertparametern verwendet werden. |
Übergabe mehrerer Zeilen in früheren Versionen von SQL Server
Vor der Einführung von Tabellenwertparametern gab es 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 Methode muss die Prozedur oder Anweisung die erforderliche Logik zum Überprüfen der Datenstrukturen und Entflechten der Werte enthalten.
Erstellt eine Reihe einzelner SQL-Anweisungen für Datenänderungen, die mehrere Zeilen betreffen, wie sie etwa durch den Aufruf der Methode
Updateeines SqlDataAdapter erstellt werden. Änderungen können einzeln an den Server übermittelt oder in Gruppen zusammengefasst werden. Auch wenn sie in Batches übermittelt werden, die mehrere Anweisungen enthalten, wird jede Anweisung einzeln auf dem Server ausgeführt.Verwenden des
bcp-Hilfsprogramms oder des SqlBulkCopy-Objekts, um eine Vielzahl von Datenzeilen in eine Tabelle zu laden. Obgleich dieses Verfahren effizient ist, wird die serverseitige Verarbeitung nur dann unterstützt, wenn die Daten in eine temporäre Tabelle oder Tabellenvariable geladen werden.
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, damit Sie Tabellenwertparameter in Ihren Clientanwendungen verwenden können. Weitere Informationen zum Erstellen von Tabellentypen finden Sie im Artikel zur Verwendung von Tabellenwertparameter (Datenbank-Engine).
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. Beim Deklarieren eines Tabellenwertparameters ist das Schlüsselwort READONLY erforderlich.
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 einem JOIN in einer FROM-Klausel verwenden, müssen Sie auch einen Alias angeben, wie in diesem Beispiel gezeigt (der Alias des Tabellenwertparameters lautet „ec“):
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 bei Tabellenwertparametern
Für Tabellenwertparameter gelten verschiedene Einschränkungen:
Tabellenwertparameter können nicht an benutzerdefinierte CLR-Funktionen übergeben werden.
Tabellenwertparameter können nur für die Unterstützung der UNIQUE- oder PRIMARY KEY-Einschränkungen indiziert werden. SQL Server verwaltet keine Statistiken für Tabellenwertparameter.
Tabellenwertparameter sind in Transact-SQL-Code schreibgeschützt. Sie können die Spaltenwerte in den Zeilen eines Tabellenwertparameters nicht aktualisieren und keine Zeilen einfügen oder löschen. Um die an eine gespeicherte Prozedur oder eine parametrisierte Anweisung in einem Tabellenwertparameter übergebenen Daten zu ändern, müssen Sie die Daten in eine temporäre Tabelle oder in eine Tabellenvariable einfügen.
Sie können keine ALTER TABLE-Anweisungen verwenden, um das Design von tabellenwertigen Parametern zu ändern.
Konfigurieren eines SqlParameter-Beispiels
Microsoft.Data.SqlClient unterstützt das Auffüllen von Tabellenwertparametern aus DataTable-, DbDataReader- und IEnumerable<T> \ SqlDataRecord-Objekten. Geben Sie mit der TypeName-Eigenschaft eines SqlParameter einen Typnamen für den Tabellenwertparameter an.
TypeName muss dem Namen eines kompatiblen Typs entsprechen, der zuvor auf dem Server erstellt wurde. Das folgende Codefragment zeigt, wie SqlParameter für das Einfügen von Daten konfiguriert wird.
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";
Sie können auch ein beliebiges von DbDataReader abgeleitetes Objekt verwenden, um Datenzeilen an einen Tabellenwertparameter zu streamen, wie im folgenden Fragment gezeigt:
// 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;
Übergeben eines Tabellenwertparameters an eine gespeicherte Prozedur
In diesem Beispiel wird veranschaulicht, wie Daten aus Tabellenwertparametern an eine gespeicherte Prozedur übergeben werden. Der Code extrahiert hinzugefügte Zeilen mithilfe der DataTable-Methode in eine neue GetChanges. Im Code wird dann ein SqlCommand definiert, wobei für die Eigenschaft CommandType der Wert StoredProcedure festgelegt wird. Der SqlParameter wird mithilfe der Methode AddWithValue gefüllt und SqlDbType wird auf Structured festgelegt. Anschließend wird der SqlCommand mit der Methode ExecuteNonQuery ausgeführt.
// 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();
}
Übergeben eines Tabellenwertparameters an eine parametrisierte SQL-Anweisung
Das folgende Beispiel veranschaulicht, wie Daten in die Tabelle dbo.Categories eingefügt werden, indem eine INSERT-Anweisung mit einer SELECT-Unterabfrage verwendet wird, die einen Tabellenwertparameter als Datenquelle verwendet. Wenn Sie einen Tabellenwertparameter an eine parametrisierte SQL-Anweisung übergeben, müssen Sie mithilfe der neuen Eigenschaft TypeName eines SqlParameter einen Typnamen für den Tabellenwertparameter angeben.
TypeName muss dem Namen eines kompatiblen Typs entsprechen, der zuvor auf dem Server erstellt wurde. Der Code in diesem Beispiel verwendet die Eigenschaft TypeName, um auf die in „dbo.CategoryTableType“ definierte Typstruktur zu verweisen.
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();
}
Zeilen mit einem DataReader übertragen
Sie können auch jedes von DbDataReader abgeleitete Objekt verwenden, um Datenzeilen an einen Tabellenwertparameter zu streamen. Das folgende Codefragment zeigt, wie Daten mithilfe von OracleCommand und OracleDataReader aus einer Oracle-Datenbank abgerufen werden. Anschließend wird mit dem Code ein SqlCommand konfiguriert, um eine gespeicherte Prozedur mit einem einzigen Eingabeparameter aufzurufen. Für die Eigenschaft SqlDbType des SqlParameter wird der Wert Structured festgelegt. Mithilfe von AddWithValue wird das Resultset OracleDataReader als Tabellenwertparameter an die gespeicherte Prozedur übergeben.
// 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();