Возвращающие табличное значение параметрыTable-Valued Parameters

Возвращающие табличное значение параметры обеспечивают легкий способ упаковки строк данных из клиентского приложения в SQL Server, не требуя многочисленных циклов приема-передачи или специальной логики на стороне сервера для обработки данных.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. Возвращающие табличное значение параметры можно использовать для инкапсуляции строк данных в клиентском приложении и отправки данных на сервер с помощью одной параметризированной команды.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. Входящие строки данных сохраняются в табличную переменную, с которой затем можно работать, используя язык Transact-SQL.The incoming data rows are stored in a table variable that can then be operated on by using Transact-SQL.

Доступ к значениям столбца в возвращающих табличное значение параметрах обеспечивается с помощью стандартных инструкций Transact-SQL SELECT.Column values in table-valued parameters can be accessed using standard Transact-SQL SELECT statements. Возвращающие табличное значение параметры строго типизированы, а проверка их структуры выполняется автоматически.Table-valued parameters are strongly typed and their structure is automatically validated. Объем, занимаемый возвращающими табличное значение параметрами, ограничен только размерами памяти на сервере.The size of table-valued parameters is limited only by server memory.

Примечание

В возвращающий табличное значение параметр нельзя вернуть данные.You cannot return data in a table-valued parameter. Возвращающие табличное значение параметры являются исключительно входными. Ключевое слово OUTPUT не поддерживается.Table-valued parameters are input-only; the OUTPUT keyword is not supported.

Дополнительные сведения о возвращающих табличное значение параметрах см. в приведенных ниже ресурсах.For more information about table-valued parameters, see the following resources.

ResourceResource ОписаниеDescription
Возвращающие табличное значение Параметры (ядро СУБД) в Электронная документация на SQL ServerTable-Valued Parameters (Database Engine) in SQL Server Books Online Описывается создание и использование возвращающих табличное значение параметров.Describes how to create and use table-valued parameters.
Определяемые пользователем табличные типы в Электронная документация на SQL ServerUser-Defined Table Types in SQL Server Books Online Описывается использование определяемых пользователем табличных типов, предназначенных для объявления возвращающих табличное значение параметров.Describes user-defined table types that are used to declare table-valued parameters.

Передача нескольких строк в предыдущие версии SQL ServerPassing Multiple Rows in Previous Versions of SQL Server

Перед тем как возвращающие табличное значение параметры были введены в SQL Server 2008, параметры передачи нескольких строк данных в хранимую процедуру или параметризованную команду SQL были ограничены.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. При передаче нескольких строк на сервер разработчик может воспользоваться одним из описанных ниже вариантов.A developer could choose from the following options for passing multiple rows to the server:

  • Использование ряда отдельных параметров, представляющих значения в нескольких столбцах и строках данных.Use a series of individual parameters to represent the values in multiple columns and rows of data. Объем данных, который можно передать с помощью этого метода, ограничен максимально допустимым количеством параметров.The amount of data that can be passed by using this method is limited by the number of parameters allowed. В процедурах SQL Server можно использовать максимум 2100 параметров.SQL Server procedures can have, at most, 2100 parameters. Для сборки отдельных значений в табличную переменную или временную таблицу для дальнейшей обработки необходимо реализовать логику на стороне сервера.Server-side logic is required to assemble these individual values into a table variable or a temporary table for processing.

  • Объединение нескольких значений данных в строки с разделителями или XML-документы и последующая передача этих текстовых значений в процедуру или инструкцию.Bundle multiple data values into delimited strings or XML documents and then pass those text values to a procedure or statement. При этом в процедуру или инструкцию необходимо включить логику для проверки структур данных и разделения значений.This requires the procedure or statement to include the logic necessary for validating the data structures and unbundling the values.

  • Создание ряда отдельных инструкций SQL для изменения данных в нескольких строках, например инструкций, создаваемых при вызове метода Update класса 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. Изменения можно отправить на сервер по отдельности или объединенными в группы.Changes can be submitted to the server individually or batched into groups. Тем не менее, даже при отправке в пакетах из нескольких инструкций, каждая инструкция выполняется на сервере отдельно.However, even when submitted in batches that contain multiple statements, each statement is executed separately on the server.

  • Чтобы загрузить несколько строк данных в таблицу, воспользуйтесь служебной программой bcp или объектом SqlBulkCopy.Use the bcp utility program or the SqlBulkCopy object to load many rows of data into a table. Хотя этот метод весьма эффективен, в нем не поддерживается обработка данных на стороне сервера, если данные не загружены во временную таблицу или табличную переменную.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.

Создание типов параметров, возвращающих табличное значениеCreating Table-Valued Parameter Types

Возвращающие табличное значение параметры основаны на строго типизированных табличных структурах, заданных с помощью инструкций Transact-SQL CREATE TYPE.Table-valued parameters are based on strongly-typed table structures that are defined by using Transact-SQL CREATE TYPE statements. Перед использованием в клиентских приложениях возвращающих табличное значение параметров в 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. Дополнительные сведения о создании табличных типов см. в разделе определяемые пользователем табличные типы в Электронная документация на SQL Server.For more information about creating table types, see User-Defined Table Types in SQL Server Books Online.

Приведенная ниже инструкция создает табличный тип с именем CategoryTableType, которая состоит из столбцов CategoryID и 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) )  

После создания табличного типа можно объявить возвращающие табличное значение параметры, основанные на этом типе.After you create a table type, you can declare table-valued parameters based on that type. В приведенном ниже фрагменте кода Transact-SQL демонстрируется объявление возвращающего табличное значение параметра в определении хранимой процедуры.The following Transact-SQL fragment demonstrates how to declare a table-valued parameter in a stored procedure definition. Обратите внимание, что для объявления возвращающего табличное значение параметра необходимо использовать ключевое слово READONLY.Note that the READONLY keyword is required for declaring a table-valued parameter.

CREATE PROCEDURE usp_UpdateCategories   
    (@tvpNewCategories dbo.CategoryTableType READONLY)  

Изменение данных с помощью возвращающих табличное значение параметров (Transact-SQL)Modifying Data with Table-Valued Parameters (Transact-SQL)

Возвращающие табличное значение параметры можно использовать для изменения данных на основе наборов, при котором в одной инструкции затрагивается несколько строк.Table-valued parameters can be used in set-based data modifications that affect multiple rows by executing a single statement. Например, можно выбрать все строки возвращающего табличное значение параметра и вставить их в таблицу базы данных или создать инструкцию UPDATE, соединив возвращающий табличное значение параметр с таблицей, которую необходимо обновить.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.

В приведенной ниже инструкции Transact-SQL UPDATE демонстрируется соединение возвращающего табличного значения параметра с таблицей Categories.The following Transact-SQL UPDATE statement demonstrates how to use a table-valued parameter by joining it to the Categories table. При использовании возвращающего табличное значение параметра в операторе JOIN предложения FROM необходимо создать для этого параметра псевдоним; как показано здесь, для возвращающего табличное значение параметра используется псевдоним «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;  

В данном примере кода Transact-SQL демонстрируется выбор строк из возвращающего табличное значение параметра для выполнения инструкции INSERT в одной операции на основе набора данных.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;  

Ограничения возвращающих табличное значение параметровLimitations of Table-Valued Parameters

На использование возвращающих табличное значение параметров накладывается ряд указанных ниже ограничений.There are several limitations to table-valued parameters:

  • Невозможно передать возвращающие табличное значение параметры в определяемые пользователем функции CLR.You cannot pass table-valued parameters to CLR user-defined functions.

  • Возвращающие табличное значение параметры можно индексировать только для поддержки ограничений UNIQUE и PRIMARY KEY.Table-valued parameters can only be indexed to support UNIQUE or PRIMARY KEY constraints. SQL Server не ведет статистику для возвращающих табличное значение параметров.SQL Server does not maintain statistics on table-valued parameters.

  • В коде Transact-SQL возвращающие табличное значение параметры предназначены только для чтения.Table-valued parameters are read-only in Transact-SQL code. Значения столбцов в строках возвращающего табличное значение параметра нельзя обновить; также нельзя вставлять и удалять строки.You cannot update the column values in the rows of a table-valued parameter and you cannot insert or delete rows. Чтобы изменить данные, передаваемые в хранимую процедуру или параметризованную инструкцию через возвращающий табличное значение параметр, необходимо вставить данные во временную таблицу или табличную переменную.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.

  • Для изменения структуры возвращающего табличное значение параметра нельзя использовать инструкции ALTER TABLE.You cannot use ALTER TABLE statements to modify the design of table-valued parameters.

Пример настройки параметра SqlParameterConfiguring a SqlParameter Example

System.Data.SqlClientподдерживает заполнение возвращающих табличное DataTableзначение DbDataReader параметров IEnumerable<T> из объектов или \ SqlDataRecord .System.Data.SqlClient supports populating table-valued parameters from DataTable, DbDataReader or IEnumerable<T> \ SqlDataRecord objects. Необходимо указать имя типа возвращающего табличное значение параметра с помощью свойства TypeName объекта SqlParameter.You must specify a type name for the table-valued parameter by using the TypeName property of a SqlParameter. Значение TypeName должно совпадать с именем совместимого типа, созданного ранее на сервере.The TypeName must match the name of a compatible type previously created on the server. В приведенном ниже фрагменте кода демонстрируется настройка объекта SqlParameter для вставки данных.The following code fragment demonstrates how to configure SqlParameter to insert data.

В следующем примере addedCategories переменная DataTableсодержит.In the following example, the addedCategories variable contains a DataTable. Чтобы увидеть, как заполняется переменная, см. примеры в следующем разделе передача возвращающего табличное значение параметра хранимой процедуре.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"  

Также для передачи строк данных в возвращающий табличное значение параметр можно использовать любой производный от DbDataReader объект, как показано в этом фрагменте.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  

Передача возвращающего табличное значение параметра в хранимую процедуруPassing a Table-Valued Parameter to a Stored Procedure

В данном примере демонстрируется передача возвращающего табличное значение параметра в хранимую процедуру.This example demonstrates how to pass table-valued parameter data to a stored procedure. Добавленные строки извлекаются в коде в новый объект DataTable с помощью метода GetChanges.The code extracts added rows into a new DataTable by using the GetChanges method. Затем в коде задается команда SqlCommand, присваивающая свойству CommandType значение StoredProcedure.The code then defines a SqlCommand, setting the CommandType property to StoredProcedure. Объект SqlParameter заполняется с помощью метода AddWithValue, а свойству SqlDbType присваивается значение Structured.The SqlParameter is populated by using the AddWithValue method and the SqlDbType is set to Structured. Затем с помощью метода выполняется SqlCommand команда 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  

Передача возвращающего табличное значение параметра в параметризованную инструкцию SQLPassing a Table-Valued Parameter to a Parameterized SQL Statement

В приведенном ниже примере кода демонстрируется вставка данных в таблицу dbo.Categories с помощью инструкции INSERT с вложенным запросом SELECT, для которого в качестве источника данных указан возвращающий табличное значение параметр.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. При передаче в параметризованную инструкцию SQL возвращающего табличное значение параметра необходимо указать для этого параметра имя типа с помощью нового свойства TypeName класса 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 должно совпадать с именем совместимого типа, созданного ранее на сервере.This TypeName must match the name of a compatible type previously created on the server. Для ссылки на структуру типов, заданную в dbo.CategoryTableType, в данном примере кода используется свойство TypeName.The code in this example uses the TypeName property to reference the type structure defined in dbo.CategoryTableType.

Примечание

При указании значения для столбца идентификаторов в возвращающем табличное значение параметре необходимо выполнить для сеанса инструкцию SET IDENTITY_INSERT.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  

Потоковая передача строк с помощью объекта DataReaderStreaming Rows with a DataReader

Также для потоковой передачи строк данных в возвращающий табличное значение параметр можно использовать любой производный от DbDataReader класс.You can also use any object derived from DbDataReader to stream rows of data to a table-valued parameter. В приведенном ниже фрагменте кода демонстрируется загрузка данных из базы данных Oracle с помощью OracleCommand и OracleDataReader.The following code fragment demonstrates retrieving data from an Oracle database by using an OracleCommand and an OracleDataReader. Затем в коде настраивается команда SqlCommand, предназначенная для вызова хранимой процедуры с одним входным параметром.The code then configures a SqlCommand to invoke a stored procedure with a single input parameter. Свойству SqlDbType класса SqlParameter присваивается значение Structured.The SqlDbType property of the SqlParameter is set to Structured. Метод AddWithValue передает результирующий набор OracleDataReader в хранимую процедуру в виде возвращающего табличное значение параметра.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()  

См. такжеSee also