Parametri valutati a livello di tabellaTable-Valued Parameters

I parametri valutati a livello di tabella consentono di eseguire facilmente il marshaling di più righe di dati di un'applicazione client in SQL Server senza richiedere più round trip o logica speciale lato server per l'elaborazione dei dati.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. È possibile usare i parametri con valori di tabella per incapsulare le righe di dati in un'applicazione client e inviare i dati al server in un singolo comando con parametri.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. Le righe di dati in arrivo vengono archiviate in una variabile di tabella che può quindi essere usata tramite Transact-SQLTransact-SQL.The incoming data rows are stored in a table variable that can then be operated on by using Transact-SQLTransact-SQL.

I valori di colonna nei parametri con valori di tabella sono accessibili tramite istruzioni SELECT Transact-SQLTransact-SQL standard.Column values in table-valued parameters can be accessed using standard Transact-SQLTransact-SQL SELECT statements. I parametri con valori di tabella sono fortemente tipizzati e la loro struttura viene convalidata automaticamente.Table-valued parameters are strongly typed and their structure is automatically validated. La dimensione dei parametri valutati a livello di tabella è limitata solo dalla memoria del server.The size of table-valued parameters is limited only by server memory.

Nota

Non è possibile restituire dati in un parametro valutato a livello di tabella.You cannot return data in a table-valued parameter. I parametri valutati a livello di tabella sono di solo input. La parola chiave OUTPUT non è supportata.Table-valued parameters are input-only; the OUTPUT keyword is not supported.

Per altre informazioni sui parametri con valori di tabella, vedere le risorse seguenti.For more information about table-valued parameters, see the following resources.

RisorsaResource DescrizioneDescription
Parametri con valori di tabella (motore di Database) nella documentazione Online di SQL ServerTable-Valued Parameters (Database Engine) in SQL Server Books Online Viene descritto come creare e usare i parametri con valori di tabella.Describes how to create and use table-valued parameters.
Tipi di tabella definiti dall'utente nella documentazione Online di SQL ServerUser-Defined Table Types in SQL Server Books Online Vengono descritti i tipi di tabella definiti dall'utente usati per dichiarare i parametri con valori di tabella.Describes user-defined table types that are used to declare table-valued parameters.

Passaggio di più righe nelle versioni precedenti di SQL ServerPassing Multiple Rows in Previous Versions of SQL Server

Prima di parametri con valori di tabella sono stati introdotti in SQL Server 2008, le opzioni per passare più righe di dati a una stored procedure o un comando SQL con parametri erano limitate.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. Per passare più righe al server, uno sviluppatore poteva scegliere tra le opzioni seguenti:A developer could choose from the following options for passing multiple rows to the server:

  • Usare una serie di parametri singoli per rappresentare i valori in più colonne e righe di dati.Use a series of individual parameters to represent the values in multiple columns and rows of data. La quantità di dati che è possibile passare tramite questo metodo è limitata dal numero di parametri consentiti.The amount of data that can be passed by using this method is limited by the number of parameters allowed. Le routine di SQL Server possono includere al massimo 2100 parametri.SQL Server procedures can have, at most, 2100 parameters. Per assemblare questi singoli valori in una variabile di tabella o in una tabella temporanea per l'elaborazione è necessaria la logica sul lato server.Server-side logic is required to assemble these individual values into a table variable or a temporary table for processing.

  • Aggregare più valori di dati in stringhe delimitate o in documenti XML, quindi passare tali valori di testo a una routine o a un'istruzione.Bundle multiple data values into delimited strings or XML documents and then pass those text values to a procedure or statement. A tale scopo, è necessario che la routine o l'istruzione includa la logica necessaria per la convalida delle strutture di dati e la separazione dei valori.This requires the procedure or statement to include the logic necessary for validating the data structures and unbundling the values.

  • Creare una serie di istruzioni SQL singole per le modifiche ai dati che riguardano più righe, ad esempio quelle create chiamando il metodo Update di un oggetto SqlDataAdapter.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. Le modifiche possono essere inviate al server individualmente o raggruppate in batch.Changes can be submitted to the server individually or batched into groups. Anche in caso di invio in batch che contengono più istruzioni, ogni istruzione viene tuttavia eseguita separatamente nel server.However, even when submitted in batches that contain multiple statements, each statement is executed separately on the server.

  • Usare l'utilità bcp o l'oggetto SqlBulkCopy per caricare numerose righe di dati in una tabella.Use the bcp utility program or the SqlBulkCopy object to load many rows of data into a table. Sebbene questa tecnica sia molto efficace, non supporta l'elaborazione sul lato server, a meno che i dati non vengano caricati in una tabella temporanea o in una variabile di tabella.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.

Creazione di tipi di parametri con valori di tabellaCreating Table-Valued Parameter Types

I parametri con valori di tabella sono basati su strutture di tabella fortemente tipizzate definite tramite istruzioni CREATE TYPE Transact-SQLTransact-SQL.Table-valued parameters are based on strongly-typed table structures that are defined by using Transact-SQLTransact-SQL CREATE TYPE statements. Per poter utilizzare i parametri con valori di tabella nelle applicazioni client, è prima necessario creare un tipo di tabella e definire la struttura in SQL Server.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. Per ulteriori informazioni sulla creazione di tipi di tabella, vedere tipi di tabella definiti dall'utente nella documentazione Online di SQL Server.For more information about creating table types, see User-Defined Table Types in SQL Server Books Online.

L'istruzione seguente consente di creare un tipo di tabella denominato CategoryTableType, costituito dalle colonne CategoryID e CategoryName: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) )  

Dopo aver creato un tipo di tabella, è possibile dichiarare i parametri con valori di tabella basati su tale tipo.After you create a table type, you can declare table-valued parameters based on that type. Nel frammento Transact-SQLTransact-SQL seguente viene illustrato come dichiarare un parametro con valori di tabella in una definizione di stored procedure.The following Transact-SQLTransact-SQL fragment demonstrates how to declare a table-valued parameter in a stored procedure definition. Si noti che, per dichiarare un parametro con valori di tabella, è necessaria la parola chiave READONLY.Note that the READONLY keyword is required for declaring a table-valued parameter.

CREATE PROCEDURE usp_UpdateCategories   
    (@tvpNewCategories dbo.CategoryTableType READONLY)  

Modifica di dati con i parametri con valori di tabella (Transact-SQL)Modifying Data with Table-Valued Parameters (Transact-SQL)

I parametri con valori di tabella possono essere usati nelle modifiche dei dati basate su set che riguardano più righe eseguendo un'unica istruzione.Table-valued parameters can be used in set-based data modifications that affect multiple rows by executing a single statement. È ad esempio possibile selezionare tutte le righe in un parametro con valori di tabella e inserirle in una tabella di database oppure creare un'istruzione di aggiornamento tramite l'unione in join di un parametro con valori di tabella con la tabella che si desidera aggiornare.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.

L'istruzione UPDATE Transact-SQLTransact-SQL seguente illustra come usare un parametro con valori di tabella tramite la sua unione in join con la tabella Categories.The following Transact-SQLTransact-SQL UPDATE statement demonstrates how to use a table-valued parameter by joining it to the Categories table. Quando si usa un parametro con valori di tabella con un JOIN in una clausola FROM, è anche necessario usare un alias per il parametro, come illustrato di seguito, dove per il parametro con valori di tabella viene usato l'alias "ec":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;  

In questo esempio Transact-SQLTransact-SQL viene illustrato come selezionare le righe da un parametro con valori di tabella per eseguire un'istruzione INSERT in una singola operazione basata su set.This Transact-SQLTransact-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;  

Limitazioni relative ai parametri con valori di tabellaLimitations of Table-Valued Parameters

Per i parametri con valori di tabella sono previste diverse limitazioni:There are several limitations to table-valued parameters:

  • È possibile passare parametri con valori di tabella per funzioni CLR definite dall'utente.You cannot pass table-valued parameters to CLR user-defined functions.

  • I parametri con valori di tabella possono essere indicizzati solo per supportare vincoli UNIQUE o PRIMARY KEY.Table-valued parameters can only be indexed to support UNIQUE or PRIMARY KEY constraints. In SQL Server non vengono gestite statistiche relative ai parametri con valori di tabella.SQL Server does not maintain statistics on table-valued parameters.

  • I parametri con valori di tabella sono di sola lettura nel codice Transact-SQLTransact-SQL.Table-valued parameters are read-only in Transact-SQLTransact-SQL code. Non è possibile aggiornare i valori delle colonne nelle righe di un parametro con valori di tabella e non è possibile inserire o eliminare righe.You cannot update the column values in the rows of a table-valued parameter and you cannot insert or delete rows. Per modificare i dati passati a una stored procedure o a un'istruzione con parametri in un parametro con valori di tabella, è necessario inserire i dati in una tabella temporanea o in una variabile di tabella.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.

  • Non è possibile usare istruzioni ALTER TABLE per modificare la struttura dei parametri con valori di tabella.You cannot use ALTER TABLE statements to modify the design of table-valued parameters.

Configurazione di un esempio SqlParameterConfiguring a SqlParameter Example

System.Data.SqlClient supporta il popolamento dei parametri con valori di tabella dal DataTable, DbDataReader oppure IEnumerable<T> \ SqlDataRecord oggetti.System.Data.SqlClient supports populating table-valued parameters from DataTable, DbDataReader or IEnumerable<T> \ SqlDataRecord objects. È necessario specificare un nome di tipo per il parametro con valori di tabella usando la proprietà TypeName di un oggetto SqlParameter.You must specify a type name for the table-valued parameter by using the TypeName property of a SqlParameter. TypeName deve corrispondere al nome di un tipo compatibile creato in precedenza nel server.The TypeName must match the name of a compatible type previously created on the server. Nel frammento di codice seguente viene illustrato come configurare SqlParameter per inserire dati.The following code fragment demonstrates how to configure SqlParameter to insert data.

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

È anche possibile usare qualsiasi oggetto derivato da DbDataReader per trasmettere flussi di righe di dati a un parametro con valori di tabella, come illustrato in questo frammento: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  

Passaggio di un parametro con valori di tabella a una stored procedurePassing a Table-Valued Parameter to a Stored Procedure

In questo esempio viene illustrato come passare i dati di un parametro con valori di tabella a una stored procedure.This example demonstrates how to pass table-valued parameter data to a stored procedure. Il codice consente di estrarre le righe aggiunte in un nuovo oggetto DataTable tramite il metodo GetChanges.The code extracts added rows into a new DataTable by using the GetChanges method. Viene quindi definito un oggetto SqlCommand, impostando la proprietà CommandType su StoredProcedure.The code then defines a SqlCommand, setting the CommandType property to StoredProcedure. L'oggetto SqlParameter viene popolato usando il metodo AddWithValue e la proprietà SqlDbType viene impostata su Structured.The SqlParameter is populated by using the AddWithValue method and the SqlDbType is set to Structured. Viene quindi eseguito SqlCommand usando il metodo ExecuteNonQuery.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  

Passaggio di un parametro con valori di tabella a un'istruzione SQL con parametriPassing a Table-Valued Parameter to a Parameterized SQL Statement

Nell'esempio seguente viene illustrato come inserire i dati nella tabella dbo.Categories tramite un'istruzione INSERT con una sottoquery SELECT che dispone di un parametro con valori di tabella come origine dati.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. Quando si passa un parametro con valori di tabella a un'istruzione SQL con parametri, è necessario specificare un nome di tipo per il parametro con valori di tabella usando la nuova proprietà TypeName di un oggetto SqlParameter.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. TypeName deve corrispondere al nome di un tipo compatibile creato in precedenza nel server.This TypeName must match the name of a compatible type previously created on the server. Nel codice di questo esempio viene usata la proprietà TypeName per fare riferimento alla struttura di tipi definita in dbo.CategoryTableType.The code in this example uses the TypeName property to reference the type structure defined in dbo.CategoryTableType.

Nota

Se si fornisce un valore per una colonna Identity in un parametro con valori di tabella, è necessario eseguire l'istruzione SET IDENTITY_INSERT per la sessione.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  

Trasmissione di flussi di righe con un oggetto DataReaderStreaming Rows with a DataReader

È anche possibile usare qualsiasi oggetto derivato da DbDataReader per trasmettere flussi di righe di dati a un parametro con valori di tabella.You can also use any object derived from DbDataReader to stream rows of data to a table-valued parameter. Nel frammento di codice seguente viene illustrato il recupero di dati da un database Oracle tramite un oggetto OracleCommand e un oggetto OracleDataReader.The following code fragment demonstrates retrieving data from an Oracle database by using an OracleCommand and an OracleDataReader. Nel codice viene quindi configurato un oggetto SqlCommand per richiamare una stored procedure con un singolo parametro di input.The code then configures a SqlCommand to invoke a stored procedure with a single input parameter. La proprietà SqlDbType di SqlParameter è impostata su Structured.The SqlDbType property of the SqlParameter is set to Structured. AddWithValue passa il set di risultati di OracleDataReader alla stored procedure come parametro con valori di tabella.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()  

Vedere ancheSee Also

Configurazione di parametri e tipi di dati dei parametriConfiguring Parameters and Parameter Data Types
Comandi e parametriCommands and Parameters
Parametri DataAdapterDataAdapter Parameters
Operazioni sui dati SQL Server in ADO.NETSQL Server Data Operations in ADO.NET
Provider gestiti ADO.NET e Centro per sviluppatori di set di datiADO.NET Managed Providers and DataSet Developer Center