TabellenwertparameterTable-Valued Parameters

Tabellenwertparameter bieten eine gute Möglichkeit, mehrere Datenzeilen aus einer Clientanwendung nach SQL Server zu marshallen, ohne dass mehrere Roundtrips oder eine besondere serverseitige Logik für die Verarbeitung der Daten notwendig ist.Table-valued parameters provide an easy way to marshal multiple rows of data from a client application to SQL Server without requiring multiple round trips or special server-side logic for processing the data. Sie können Tabellenwertparameter verwenden, um Datenzeilen in einer Clientanwendung zu kapseln und diese Daten in einem einzelnen parametrisierten Befehl an den Server zu senden.You can use table-valued parameters to encapsulate rows of data in a client application and send the data to the server in a single parameterized command. Die eingehenden Datenzeilen werden in einer Tabellenvariablen gespeichert, die anschließend mit Transact-SQL verarbeitet werden kann.The incoming data rows are stored in a table variable that can then be operated on by using Transact-SQL.

Für den Zugriff auf Spaltenwerte in Tabellenwertparametern können Standard-SELECT-Anweisungen in Transact-SQL verwendet werden.Column values in table-valued parameters can be accessed using standard Transact-SQL SELECT statements. Tabellenwertparameter sind stark typisiert, und die Überprüfung ihrer Struktur erfolgt automatisch.Table-valued parameters are strongly typed and their structure is automatically validated. Die Größe der Tabellenwertparameter wird nur vom Serverarbeitsspeicher beschränkt.The size of table-valued parameters is limited only by server memory.

Hinweis

Daten in einem Tabellenwertparameter können nicht zurückgegeben werden.You cannot return data in a table-valued parameter. Tabellenwertparameter sind reine Eingabeparameter; das OUTPUT-Schlüsselwort wird nicht unterstützt.Table-valued parameters are input-only; the OUTPUT keyword is not supported.

Weitere Informationen über Tabellenwertparameter finden Sie in den folgenden Ressourcen.For more information about table-valued parameters, see the following resources.

RessourceResource BeschreibungDescription
Tabellenwert Parameter (Datenbank-Engine) in SQL Server-OnlinedokumentationTable-Valued Parameters (Database Engine) in SQL Server Books Online Beschreibt das Erstellen und Verwenden von Tabellenwertparametern.Describes how to create and use table-valued parameters.
Benutzerdefinierte Tabellentypen in SQL Server-OnlinedokumentationUser-Defined Table Types in SQL Server Books Online Beschreibt benutzerdefinierte Tabellentypen, die zum Deklarieren von Tabellenwertparametern verwendet werden.Describes user-defined table types that are used to declare table-valued parameters.

Übergeben von mehreren Zeilen in älteren Versionen von SQL ServerPassing Multiple Rows in Previous Versions of SQL Server

Vor der Einführung von Tabellenwert Parametern in SQL Server 2008 waren die Optionen zum Übergeben mehrerer Daten Zeilen an eine gespeicherte Prozedur oder einen parametrisierten SQL-Befehl eingeschränkt.Before table-valued parameters were introduced to SQL Server 2008, the options for passing multiple rows of data to a stored procedure or a parameterized SQL command were limited. Ein Entwickler konnte aus den folgenden Optionen zum Übergeben mehrerer Zeilen an den Server auswählen:A developer could choose from the following options for passing multiple rows to the server:

  • Er hatte die Möglichkeit, eine Reihe einzelner Parameter zu verwenden, um die Werte in mehreren Spalten und Zeilen von Daten darzustellen.Use a series of individual parameters to represent the values in multiple columns and rows of data. Die Datenmenge, die mithilfe dieser Methode übergeben werden kann, ist durch die Anzahl der zulässigen Parameter beschränkt.The amount of data that can be passed by using this method is limited by the number of parameters allowed. SQL Server-Prozeduren können höchstens über 2100 Parameter verfügen.SQL Server procedures can have, at most, 2100 parameters. Eine Zusammenstellung zur Verarbeitung dieser einzelnen Werte in einer Tabellenvariablen oder einer temporären Tabelle erfordert serverseitige Logik.Server-side logic is required to assemble these individual values into a table variable or a temporary table for processing.

  • Ein Entwickler hatte weiterhin die Möglichkeit, mehrere Datenwerte in voneinander getrennten Zeichenfolgen oder in XML-Dokumenten zu bündeln und diese Textwerte anschließend an eine Prozedur oder Anweisung zu übergeben.Bundle multiple data values into delimited strings or XML documents and then pass those text values to a procedure or statement. Bei dieser Methode muss die Prozedur oder Anweisung die erforderliche Logik zum Überprüfen der Datenstrukturen sowie zum Entbündeln der Werte beinhalten.This requires the procedure or statement to include the logic necessary for validating the data structures and unbundling the values.

  • Ein Entwickler konnte weiterhin eine Reihe einzelner SQL-Datenänderungsanweisungen erstellen, die mehrere Zeilen betreffen, wie beispielsweise Anweisungen, die durch den Aufruf der Update-Methode eines SqlDataAdapter erstellt werden.Create a series of individual SQL statements for data modifications that affect multiple rows, such as those created by calling the Update method of a SqlDataAdapter. Die Änderungen können dabei einzeln oder in Gruppen gestapelt an den Server gesendet werden.Changes can be submitted to the server individually or batched into groups. Jede Anweisung wird jedoch auf dem Server einzeln ausgeführt, auch wenn die Anweisungen in Stapeln übermittelt werden.However, even when submitted in batches that contain multiple statements, each statement is executed separately on the server.

  • Das bcp-Hilfsprogramm und das SqlBulkCopy-Objekt bieten ebenfalls die Möglichkeit, mehrere Datenzeilen in eine Tabelle zu laden.Use the bcp utility program or the SqlBulkCopy object to load many rows of data into a table. Obwohl diese Technik sehr effizient ist, bietet sie keine Unterstützung für serverseitige Verarbeitung, wenn die Daten nicht in eine temporäre Tabelle oder Tabellenvariable geladen werden.Although this technique is very efficient, it does not support server-side processing unless the data is loaded into a temporary table or table variable.

Erstellen von Tabellenwertparameter-TypenCreating Table-Valued Parameter Types

Tabellenwertparameter basieren auf stark typisierten Tabellenstrukturen, die mit CREATE TYPE-Anweisungen in Transact-SQL definiert werden.Table-valued parameters are based on strongly-typed table structures that are defined by using Transact-SQL CREATE TYPE statements. Sie müssen einen Tabellentyp erstellen und die Struktur in SQL Server definieren, bevor Sie Tabellenwertparameter in Ihren Clientanwendungen verwenden können.You have to create a table type and define the structure in SQL Server before you can use table-valued parameters in your client applications. Weitere Informationen zum Erstellen von Tabellentypen finden Sie unter Benutzerdefinierte Tabellentypen in SQL Server-Onlinedokumentation.For more information about creating table types, see User-Defined Table Types in SQL Server Books Online.

Die folgende Anweisung erstellt einen Tabellentyp mit dem Namen <legacyBold>CategoryTableType</legacyBold>, der aus den Spalten <legacyBold>CategoryID</legacyBold> und <legacyBold>CategoryName</legacyBold> besteht:The following statement creates a table type named CategoryTableType that consists of CategoryID and CategoryName columns:

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

Nach der Erstellung eines Tabellentyps können Tabellenwertparameter auf Grundlage dieses Typs deklariert werden.After you create a table type, you can declare table-valued parameters based on that type. Das folgende Transact-SQL-Fragment veranschaulicht, wie ein Tabellenwertparameter in der Definition einer gespeicherten Prozedur deklariert wird.The following Transact-SQL fragment demonstrates how to declare a table-valued parameter in a stored procedure definition. Beachten Sie, dass das READONLY-Schlüsselwort zum Deklarieren eines Tabellenwertparameters erforderlich ist.Note that the READONLY keyword is required for declaring a table-valued parameter.

CREATE PROCEDURE usp_UpdateCategories   
    (@tvpNewCategories dbo.CategoryTableType READONLY)  

Ändern von Daten mit Tabellenwertparametern (Transact-SQL)Modifying Data with Table-Valued Parameters (Transact-SQL)

Tabellenwertparameter können in mengenbasierten Datenänderungen verwendet werden, die mehrere Zeilen mit der Ausführung einer einzelnen Anweisung beeinflussen können.Table-valued parameters can be used in set-based data modifications that affect multiple rows by executing a single statement. So können Sie z. B. alle Zeilen in einem Tabellenwertparameter auswählen und diese in eine Datenbanktabelle einfügen, oder Sie können eine Updateanweisung erstellen, indem Sie einen Tabellenwertparameter mit der zu aktualisierenden Tabelle verknüpfen.For example, you can select all the rows in a table-valued parameter and insert them into a database table, or you can create an update statement by joining a table-valued parameter to the table you want to update.

Mit der folgenden UPDATE-Anweisung in Transact-SQL wird veranschaulicht, wie ein Tabellenwertparameter durch einen Join mit der Categories-Tabelle verwendet wird.The following Transact-SQL UPDATE statement demonstrates how to use a table-valued parameter by joining it to the Categories table. Wenn Sie einen Tabellenwertparameter mit einem JOIN in einer FROM-Klausel verwenden, müssen Sie diesen ebenfalls mit einem Alias versehen. Dies ist im folgenden Beispiel dargestellt, in dem der Tabellenwertparameter mit dem Alias "ec" versehen ist:When you use a table-valued parameter with a JOIN in a FROM clause, you must also alias it, as shown here, where the table-valued parameter is aliased as "ec":

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

Dieses Transact-SQL-Beispiel veranschaulicht, wie Zeilen aus einem Tabellenwertparameter ausgewählt werden, um einen INSERT-Vorgang in einem einzelnen mengenbasierten Vorgang auszuführen.This Transact-SQL example demonstrates how to select rows from a table-valued parameter to perform an INSERT in a single set-based operation.

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

Einschränkungen von TabellenwertparameternLimitations of Table-Valued Parameters

Es gibt mehrere Einschränkungen bei Tabellenwertparametern:There are several limitations to table-valued parameters:

  • Tabellenwert Parameter können nicht an benutzerdefinierte CLR-Funktionenübergeben werden.You cannot pass table-valued parameters to CLR user-defined functions.

  • Tabellenwertparameter können nur zur Unterstützung von UNIQUE- und PRIMARY KEY-Einschränkungen indiziert werden.Table-valued parameters can only be indexed to support UNIQUE or PRIMARY KEY constraints. SQL Server führt keine Statistik zu Tabellenwertparametern.SQL Server does not maintain statistics on table-valued parameters.

  • Tabellenwertparameter sind in Transact-SQL-Code schreibgeschützt.Table-valued parameters are read-only in Transact-SQL code. Die Spaltenwerte in den Zeilen eines Tabellenwertparameters können nicht aktualisiert werden, und Zeilen können nicht eingefügt oder gelöscht werden.You cannot update the column values in the rows of a table-valued parameter and you cannot insert or delete rows. Um die Daten zu ändern, die in einem Tabellenwertparameter an eine gespeicherte Prozedur oder eine parametrisierte Anweisung übergeben werden, müssen die Daten in eine temporäre Tabelle oder eine Tabellenvariable eingefügt werden.To modify the data that is passed to a stored procedure or parameterized statement in table-valued parameter, you must insert the data into a temporary table or into a table variable.

  • Es können keine ALTER TABLE-Anweisungen zum Ändern des Entwurfs von Tabellenwertparametern verwendet werden.You cannot use ALTER TABLE statements to modify the design of table-valued parameters.

Konfigurieren eines SqlParameter-BeispielsConfiguring a SqlParameter Example

System.Data.SqlClientunterstützt das Auffüllen von Tabellenwert Parametern DataTableaus DbDataReader - IEnumerable<T> ,-oder \ SqlDataRecord -Objekten.System.Data.SqlClient supports populating table-valued parameters from DataTable, DbDataReader or IEnumerable<T> \ SqlDataRecord objects. Mithilfe der TypeName-Eigenschaft eines SqlParameter muss ein Typname für den Tabellenwertparameter angegeben werden.You must specify a type name for the table-valued parameter by using the TypeName property of a SqlParameter. Der TypeName muss dem Namen eines kompatiblen Typs entsprechen, der zuvor auf dem Server erstellt wurde.The TypeName must match the name of a compatible type previously created on the server. Das folgende Codefragment zeigt, wie SqlParameter konfiguriert werden kann, um Daten einzufügen:The following code fragment demonstrates how to configure SqlParameter to insert data.

Im folgenden Beispiel enthält die addedCategories -Variable ein. DataTableIn the following example, the addedCategories variable contains a DataTable. Informationen zum Füllen der Variablen finden Sie in den Beispielen im nächsten Abschnitt übergeben eines Tabellenwert Parameters an eine gespeicherte Prozedur.To see how the variable is populated, see the examples in the next section, Passing a Table-Valued Parameter to a Stored 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"  

Sie können auch ein von DbDataReader abgeleitetes Objekt verwenden, um Datenzeilen als Stream in einen Tabellenwertparameter zu übertragen. Dies ist im folgenden Fragment dargestellt:You can also use any object derived from DbDataReader to stream rows of data to a table-valued parameter, as shown in this 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  

Übergeben eines Tabellenwert Parameters an eine gespeicherte ProzedurPassing a Table-Valued Parameter to a Stored Procedure

In diesem Beispiel wird veranschaulicht, wie Daten eines Tabellenwertparameters an eine gespeicherte Prozedur übergeben werden.This example demonstrates how to pass table-valued parameter data to a stored procedure. Der Code ruft hinzugefügte Zeilen mithilfe der DataTable-Methode in eine neue GetChanges ab.The code extracts added rows into a new DataTable by using the GetChanges method. Anschließend definiert der Code einen SqlCommand, mit dem die CommandType-Eigenschaft auf StoredProcedure festgelegt wird.The code then defines a SqlCommand, setting the CommandType property to StoredProcedure. Der SqlParameter wird mit der AddWithValue-Methode aufgefüllt, und der SqlDbType wird auf Structured festgelegt.The SqlParameter is populated by using the AddWithValue method and the SqlDbType is set to Structured. Der SqlCommand wird dann mithilfe der ExecuteNonQuery-Methode ausgeführt.The SqlCommand is then executed by using the ExecuteNonQuery method.

// 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 Tabellenwertparameters an eine parametrisierte SQL-AnweisungPassing a Table-Valued Parameter to a Parameterized SQL Statement

Im folgenden Beispiel wird veranschaulicht, wie Daten unter Verwendung einer INSERT-Anweisung mit einer SELECT-Unterabfrage, die über einen Tabellenwertparameter als Datenquelle verfügt, in die dbo.Categories-Tabelle eingefügt werden.The following example demonstrates how to insert data into the dbo.Categories table by using an INSERT statement with a SELECT subquery that has a table-valued parameter as the data source. Beim Übergeben eines Tabellenwertparameters an eine parametrisierte SQL-Anweisung muss ein Typname für den Tabellenwertparameter angegeben werden, indem die neue TypeName-Eigenschaft eines SqlParameter verwendet wird.When passing a table-valued parameter to a parameterized SQL statement, you must specify a type name for the table-valued parameter by using the new TypeName property of a SqlParameter. Dieser TypeName muss dem Namen eines kompatiblen Typs entsprechen, der zuvor auf dem Server erstellt wurde.This TypeName must match the name of a compatible type previously created on the server. Im Code in diesem Beispiel wird die TypeName-Eigenschaft verwendet, um auf die in <legacyBold>dbo.CategoryTableType</legacyBold> definierte Typstruktur zu verweisen.The code in this example uses the TypeName property to reference the type structure defined in dbo.CategoryTableType.

Hinweis

Wenn ein Wert für eine Identitätsspalte in einem Tabellenwertparameter angegeben wird, muss die SET IDENTITY_INSERT-Anweisung für die Sitzung ausgegeben werden.If you supply a value for an identity column in a table-valued parameter, you must issue the SET IDENTITY_INSERT statement for the session.

// 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 DataReaderStreaming Rows with a DataReader

Sie können auch ein von DbDataReader abgeleitetes Objekt verwenden, um Datenzeilen als Stream in einen Tabellenwertparameter zu übertragen.You can also use any object derived from DbDataReader to stream rows of data to a table-valued parameter. Im folgenden Codefragment wird veranschaulicht, wie Daten mithilfe eines OracleCommand und eines OracleDataReader aus einer Oracle-Datenbank abgerufen werden.The following code fragment demonstrates retrieving data from an Oracle database by using an OracleCommand and an OracleDataReader. Im Code wird dann ein SqlCommand konfiguriert, um eine gespeicherte Prozedur mit einem einzelnen Eingabeparameter aufzurufen.The code then configures a SqlCommand to invoke a stored procedure with a single input parameter. Die SqlDbType-Eigenschaft von SqlParameter wird auf Structured festgelegt.The SqlDbType property of the SqlParameter is set to Structured. Die AddWithValue-Methode übergibt das OracleDataReader-Resultset als Tabellenwertparameter an die gespeicherte Prozedur.The AddWithValue passes the OracleDataReader result set to the stored procedure as a table-valued parameter.

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