Tabellenwertparameter in SQL Server 2008 (ADO.NET)

Článek
08/21/2008

Aktualisiert: November 2007

Tabellenwertparameter bieten eine gute Möglichkeit, mehrere Datenzeilen aus einer Clientanwendung an SQL Server zu marshallen, ohne dass mehrere Roundtrips oder eine besondere serverseitige Logik für die Verarbeitung der Daten notwendig ist. 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. Die eingehenden Datenzeilen werden in einer Tabellenvariablen gespeichert, die anschließend mit Transact-SQL verarbeitet werden kann.

Auf Spaltenwerte in Tabellenwertparametern kann mit SELECT-Standardanweisungen in Transact-SQL zugegriffen werden. Tabellenwertparameter sind stark typisiert, und die Überprüfung ihrer Struktur erfolgt automatisch. Die Größe von Tabellenwertparametern ist nur durch den Serverarbeitsspeicher beschränkt.

Hinweis:
Daten in einem Tabellenwertparameter können nicht zurückgegeben werden. Tabellenwertparameter sind reine Eingabeparameter. Das OUTPUT-Schlüsselwort wird nicht unterstützt.

Weitere Informationen über Tabellenwertparameter finden Sie in den folgenden Ressourcen.

Ressource	Beschreibung
Tabellenwertparameter (Datenbankmodul) in der SQL Server-Onlinedokumentation.	Beschreibt das Erstellen und Verwenden von Tabellenwertparametern.
Benutzerdefinierte Tabellentypen in der SQL Server-Onlinedokumentation.	Beschreibt benutzerdefinierte Tabellentypen, die zum Deklarieren von Tabellenwertparametern verwendet werden.
Der Abschnitt Microsoft SQL Server-Datenbankmodul von CodePlex	Enthält Beispiele, in denen die Verwendung von SQL Server-Features und -Funktionen veranschaulicht wird.

Ü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. Ein Entwickler konnte aus den folgenden Optionen zum Übergeben mehrerer Zeilen an den Server auswählen:

Er hatte die Möglichkeit, eine Reihe einzelner Parameter zu verwenden, um die Werte in mehreren Spalten und Zeilen von Daten darzustellen. Die Datenmenge, die mithilfe dieser Methode übergeben werden kann, ist durch die Anzahl der zulässigen Parameter beschränkt. SQL Server-Prozeduren können höchstens über 2100 Parameter verfügen. Eine Zusammenstellung zur Verarbeitung dieser einzelnen Werte in einer Tabellenvariablen oder einer temporären Tabelle erfordert serverseitige Logik.
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. Bei dieser Methode muss die Prozedur oder Anweisung die erforderliche Logik zum Überprüfen der Datenstrukturen sowie zum Entbündeln der Werte beinhalten.
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. Die Änderungen können dabei einzeln oder in Gruppen gestapelt an den Server gesendet werden. Jede Anweisung wird jedoch auf dem Server einzeln ausgeführt, auch wenn die Anweisungen in Stapeln übermittelt werden.
Das bcp-Dienstprogramm und das SqlBulkCopy-Objekt bieten ebenfalls die Möglichkeit, mehrere Datenzeilen in eine Tabelle zu laden. 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.

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 über das Erstellen von Tabellentypen finden Sie unter Benutzerdefinierte Tabellentypen in der SQL Server 2008-Onlinedokumentation.

Die folgende Anweisung erstellt einen Tabellentyp mit dem Namen CategoryTableType, der aus den Spalten CategoryID und CategoryName besteht:

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. 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)

Tabellenwertparameter können in mengenbasierten Datenänderungen verwendet werden, die mehrere Zeilen mit der Ausführung einer einzelnen Anweisung beeinflussen können. 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 Aktualisierungsanweisung erstellen, indem Sie einen Tabellenwertparameter mit der zu aktualisierenden Tabelle verknüpfen.

Mit der folgenden UPDATE-Anweisung in Transact-SQL wird veranschaulicht, wie ein Tabellenwertparameter durch eine Verknüpfung mit der Categories-Tabelle verwendet wird. 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:

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.

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

Einschränkungen von Tabellenwertparametern

Es gibt mehrere Einschränkungen bei Tabellenwertparametern:

Tabellenwertparameter können nicht an benutzerdefinierte Funktionen übergeben werden.
Tabellenwertparameter können nur zur Unterstützung von UNIQUE- und PRIMARY KEY-Einschränkungen indiziert werden. SQL Server führt keine Statistik zu Tabellenwertparametern.
Tabellenwertparameter sind in Transact-SQL-Code schreibgeschützt. Die Spaltenwerte in den Zeilen eines Tabellenwertparameters können nicht aktualisiert werden, und Zeilen können nicht eingefügt oder gelöscht werden. 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.
Es können keine ALTER TABLE-Anweisungen zum Ändern des Entwurfs von Tabellenwertparametern verwendet werden.

Konfigurieren eines SqlParameter-Beispiels

System.Data.SqlClient unterstützt das Auffüllen von Tabellenwertparametern aus DataTable-, DbDataReader- und IList-Objekten. Mithilfe der TypeName-Eigenschaft eines SqlParameter muss ein Typname für den Tabellenwertparameter angegeben werden. Der TypeName muss dem Namen eines kompatiblen Typs entsprechen, der zuvor auf dem Server erstellt wurde. Das folgende Codefragment zeigt, wie SqlParameter konfiguriert werden kann, um Daten einzufügen:

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

 // 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 Tabellenwertparameters an eine gespeicherte Prozedur

In diesem Beispiel wird veranschaulicht, wie Daten eines Tabellenwertparameters an eine gespeicherte Prozedur übergeben werden. Der Code ruft hinzugefügte Zeilen mithilfe der GetChanges-Methode in eine neue DataTable ab. Anschließend definiert der Code einen SqlCommand, mit dem die CommandType-Eigenschaft auf StoredProcedure festgelegt wird. Der SqlParameter wird mit der AddWithValue-Methode aufgefüllt, und der SqlDbType wird auf Structured festgelegt. Der SqlCommand wird dann mithilfe der ExecuteNonQuery-Methode 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();
}

' 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-Anweisung

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. 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. Dieser TypeName muss dem Namen eines kompatiblen Typs entsprechen, der zuvor auf dem Server erstellt wurde. Im Code in diesem Beispiel wird die TypeName-Eigenschaft verwendet, um auf die in dbo.CategoryTableType definierte Typstruktur zu verweisen.

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.

// 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 auch ein von DbDataReader abgeleitetes Objekt verwenden, um Datenzeilen als Stream in einen Tabellenwertparameter zu übertragen. Im folgenden Codefragment wird veranschaulicht, wie Daten mithilfe eines OracleCommand und eines OracleDataReader aus einer Oracle-Datenbank abgerufen werden. Im Code wird dann ein SqlCommand konfiguriert, um eine gespeicherte Prozedur mit einem einzelnen Eingabeparameter aufzurufen. Die SqlDbType-Eigenschaft von SqlParameter wird auf Structured festgelegt. Die AddWithValue-Methode übergibt das OracleDataReader-Resultset als Tabellenwertparameter an die gespeicherte Prozedur.

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