Paramètres table

Les paramètres table fournissent un moyen simple de marshaler plusieurs lignes de données d’une application cliente vers SQL Server sans avoir recours à plusieurs allers-retours ou à une logique spéciale côté serveur pour traiter les données. Vous pouvez utiliser des paramètres table pour encapsuler des lignes de données dans une application cliente et envoyer les données au serveur dans une commande paramétrable unique. Les lignes de données entrantes sont stockées dans une variable de table que vous pouvez ensuite utiliser à l’aide de Transact-SQL.

Les valeurs de colonne dans les paramètres de table sont accessibles à l’aide d’instructions SELECT de Transact-SQL standard. Les paramètres de type table sont fortement typés et leur structure est validée automatiquement. La taille des paramètres de type table est limitée uniquement par la mémoire du serveur.

Remarque

Il n’est pas possible de retourner des données dans un paramètre de type table. Les paramètres de type table sont des paramètres d’entrée uniquement ; le mot clé OUTPUT n’est pas pris en charge.

Pour plus d’informations sur les paramètres de type table, consultez les ressources suivantes.

Ressource	Descriptif
Utiliser les paramètres avec valeur de table (du moteur de base de données)	Décrit comment créer et utiliser les paramètres de type table.
Types de tables définis par l'utilisateur	Décrit les types de tables définis par l'utilisateur utilisés pour déclarer des paramètres de type table.

Passage de plusieurs lignes dans les versions précédentes de SQL Server

Avant l’introduction des paramètres table dans SQL Server 2008, les options permettant de passer plusieurs lignes de données à une procédure stockée ou à une commande SQL paramétrée étaient limitées. Un développeur peut choisir parmi les options suivantes pour transmettre plusieurs lignes au serveur :

Utilisez une série de paramètres individuels pour représenter les valeurs dans plusieurs colonnes et lignes de données. La quantité de données transmissibles selon cette méthode est limitée par le nombre de paramètres autorisés. Les procédures SQL Server peuvent contenir jusqu’à 2 100 paramètres. Une logique côté serveur est requise pour assembler ces différentes valeurs dans une variable de table ou une table temporaire à des fins de traitement.
Regroupez plusieurs valeurs de données dans des chaînes délimitées ou des documents XML, puis transmettez ces valeurs de texte à une procédure ou une instruction. Il faut pour cela que la procédure ou l’instruction inclue la logique nécessaire pour valider les structures de données et dissocier les valeurs.
Créez une série d’instructions SQL individuelles pour les modifications de données qui affectent plusieurs lignes, telles que celles créées par la méthode Update du SqlDataAdapter. Envoyez des modifications individuellement ou par lots. Même dans un lot, le serveur exécute chaque instruction séparément.
Utilisez le programme utilitaire bcp ou l’objet SqlBulkCopy pour charger de nombreuses lignes de données dans une table. Bien que cette technique soit très efficace, elle ne prend pas en charge le traitement côté serveur, sauf si les données sont chargées dans une table ou une variable de table temporaire.

Création de types de paramètres Table-Valued

Les paramètres de table sont basés sur des structures de table fortement typées, définies à l’aide d’instructions Transact-SQL CREATE TYPE. Vous devez créer un type de table et définir la structure dans SQL Server avant de pouvoir utiliser les paramètres table dans vos applications clientes. Pour plus d’informations sur la création de types de tables, consultez User-Defined Types de tables.

L’instruction suivante crée un type table nommé CategoryTableType qui se compose de colonnes CategoryID et CategoryName :

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

Après avoir créé un type table, vous pouvez déclarer des paramètres table basés sur ce type. Le fragment Transact-SQL suivant montre comment déclarer un paramètre table dans une définition de procédure stockée. Notez que le mot clé READONLY est requis pour déclarer un paramètre de type table.

CREATE PROCEDURE usp_UpdateCategories
    (@tvpNewCategories dbo.CategoryTableType READONLY)

Modification de données avec des paramètres Table-Valued (Transact-SQL)

Les paramètres de type table peuvent être utilisés dans des modifications de données basées sur des ensembles qui affectent plusieurs lignes en exécutant une seule instruction. Par exemple, vous pouvez sélectionner toutes les lignes d’un paramètre table et les insérer dans une table de base de données, ou créer une instruction de mise à jour en joignant un paramètre table à la table à mettre à jour.

L’instruction Transact-SQL UPDATE suivante montre comment utiliser un paramètre table en le joignant à la table Categories. Lorsque vous utilisez un paramètre de type table avec un JOIN dans une clause FROM, vous devez également lui attribuer un alias, comme indiqué ici, où le paramètre de type table porte l’alias « ec » :

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

Cet exemple Transact-SQL montre comment sélectionner des lignes depuis un paramètre de table afin d’effectuer une opération INSERT en une seule opération ensembliste.

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

Limitations des paramètres de Table-Valued

Les paramètres de type table présentent plusieurs limitations :

Vous ne pouvez pas passer des paramètres table aux fonctions CLR définies par l’utilisateur.
Les paramètres de type table peuvent uniquement être indexés afin de prendre en charge les contraintes UNIQUE ou PRIMARY KEY. SQL Server ne gère pas les statistiques sur les paramètres de type table.
Les paramètres de type table sont en lecture seule dans le code Transact-SQL. Vous ne pouvez pas mettre à jour les valeurs de colonne dans les lignes d’un paramètre table et vous ne pouvez pas insérer ou supprimer des lignes. Pour modifier les données transmises à une procédure stockée ou à une instruction paramétrable dans un paramètre table, il faut les insérer dans une table temporaire ou une variable de table.
Vous ne pouvez pas utiliser des instructions ALTER TABLE pour modifier la conception des paramètres de table.

Configuration d’un exemple SqlParameter

Installez le package NuGet Microsoft.Data.SqlClient et importez Microsoft.Data.SqlClient et System.Data. Pour les séquences d’enregistrements côté client, utilisez Microsoft.Data.SqlClient.Server.SqlDataRecord et Microsoft.Data.SqlClient.Server.SqlMetaData. Ne remplacez pas les types SQL CLR côté serveur par ceux de Microsoft.SqlServer.Server.

Microsoft.Data.SqlClient prend en charge les valeurs de paramètres de table depuis DataTable, DbDataReader ou IEnumerable<T> d’objets SqlDataRecord. Définissez le TypeName du paramètre sur le nom d’un type de table compatible précédemment créé sur le serveur. Le fragment suivant configure le paramètre pour insérer des données.

Dans l’exemple ci-dessous, la variable addedCategories contient un DataTable. Pour voir comment la variable est remplie, consultez les exemples de la section suivante, Passage d'un paramètre table à une procédure stockée.

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

Vous pouvez également utiliser n’importe quel objet dérivé de l’objet DbDataReader pour transmettre en continu des lignes de données à un paramètre table, tel qu’indiqué dans ce 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

Passage d’un paramètre Table-Valued à une procédure stockée

Cet exemple transmet les données de paramètre de type table à une procédure stockée. Le code appelle GetChanges pour extraire des lignes ajoutées vers le nouveau DataTable. Il crée le SqlCommand avec CommandType défini sur StoredProcedure. Il ajoute le paramètre avec AddWithValue et définit SqlDbType à Structured. Enfin, le code appelle ExecuteNonQuery.

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

Passage d’un paramètre Table-Valued à une instruction SQL paramétrable

L’exemple suivant insère des données dans dbo.Categories à l’aide d’une instruction INSERT et d’une sous-requête SELECT qui lit le paramètre de type table. Pour une instruction SQL paramétrée, définissez TypeName sur le nom d’un type de table compatible précédemment créé sur le serveur. Cet exemple utilise dbo.CategoryTableType.

Remarque

Si vous fournissez une valeur pour une colonne d’identité dans un paramètre table, vous devez émettre l’instruction SET IDENTITY_INSERT pour la 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

Diffusion en continu des lignes à l'aide d'un objet DataReader

Vous pouvez utiliser n’importe quel objet dérivé de DbDataReader pour transmettre des lignes à un paramètre de table. Le fragment suivant récupère les données d’une base de données Oracle avec OracleCommand et OracleDataReader. Le code configure le SqlCommand pour invoquer une procédure stockée avec un paramètre d’entrée. Il définit SqlDbType sur Structured et transmet le jeu de résultats OracleDataReader en AddWithValue tant que paramètre table.

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

Voir aussi

Dernière mise à jour : 2026-10-27