Table-Valued 매개 변수

테이블 값 매개 변수는 클라이언트 애플리케이션에서 SQL Server로 여러 행의 데이터를 별도의 서버 쪽 논리나 여러 번의 왕래 없이 쉽게 전달할 수 있도록 해줍니다. 테이블 반환 매개 변수를 사용하여 클라이언트 애플리케이션의 데이터 행을 캡슐화하고 매개 변수가 있는 단일 명령으로 서버에 데이터를 보낼 수 있습니다. 들어오는 데이터 행은 Transact-SQL을 사용하여 작업할 수 있는 테이블 변수에 저장됩니다.

테이블 반환 매개 변수의 열 값은 표준 Transact-SQL SELECT 문을 사용하여 액세스할 수 있습니다. 테이블 값 매개 변수는 강력한 형식이며 해당 구조는 자동으로 검사됩니다. 테이블 반환 매개 변수의 크기는 서버 메모리에 의해서만 제한됩니다.

테이블 반환 매개 변수에 대한 자세한 내용은 다음 리소스를 참조하세요.

이전 버전의 SQL Server에서 여러 행을 전달하는 방법

SQL Server 2008에 테이블 반환 매개 변수가 도입되기 전에는 여러 행의 데이터를 저장 프로시저 또는 매개 변수가 있는 SQL 명령에 전달하는 옵션이 제한되었습니다. 개발자는 여러 행을 서버에 전달하기 위해 다음 옵션 중에서 선택할 수 있습니다.

• 일련의 개별 매개 변수를 사용하여 여러 열 및 데이터 행의 값을 나타냅니다. 이 메서드를 사용하여 전달할 수 있는 데이터의 양은 허용되는 매개 변수 수로 제한됩니다. SQL Server 프로시저에는 최대 2,100개의 매개 변수가 있을 수 있습니다. 이러한 개별 값을 테이블 변수 또는 임시 테이블로 어셈블하려면 서버 쪽 논리가 필요합니다.

• 여러 데이터 값을 구분된 문자열 또는 XML 문서에 번들로 묶은 다음 해당 텍스트 값을 프로시저 또는 문에 전달합니다. 이렇게 하려면 프로시저 또는 문에 데이터 구조의 유효성을 검사하고 값을 해제하는 데 필요한 논리가 포함되어야 합니다.

• 여러 행에 영향을 주는 데이터 수정을 위해 Update의 SqlDataAdapter 메서드를 호출하여 작성된 것과 같은 일련의 개별 SQL 문을 만듭니다. 변경 내용을 서버에 개별적으로 제출하거나 그룹으로 일괄 처리할 수 있습니다. 그러나 여러 문을 포함하는 일괄 처리로 제출된 경우에도 각 문은 서버에서 별도로 실행됩니다.

• bcp 유틸리티 프로그램 또는 개체를 SqlBulkCopy 사용하여 많은 데이터 행을 테이블에 로드합니다. 이 기술은 매우 효율적이지만 데이터가 임시 테이블 또는 테이블 변수에 로드되지 않는 한 서버 쪽 처리를 지원하지 않습니다.

Table-Valued 매개 변수 형식 만들기

테이블 값 매개변수는 SQL의 Transact-SQL CREATE TYPE 문을 사용하여 정의된 강력한 형식의 테이블 구조를 기반으로 합니다. 클라이언트 애플리케이션에서 테이블 반환 매개 변수를 사용하려면 SQL Server에서 테이블 형식을 만들고 구조를 정의해야 합니다. 테이블 형식을 만드는 방법에 대한 자세한 내용은 User-Defined Table Types을 참조하세요.

다음 문은 CategoryID 및 CategoryName 열로 구성된 CategoryTableType이라는 테이블 형식을 만듭니다.

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

테이블 형식을 만든 후 해당 형식에 따라 테이블 반환 매개 변수를 선언할 수 있습니다. 다음 Transact-SQL 조각에서는 저장 프로시저 정의에서 테이블 반환 매개 변수를 선언하는 방법을 보여 줍니다. 테이블 반환 매개 변수를 선언하려면 READONLY 키워드가 필요합니다.

CREATE PROCEDURE usp_UpdateCategories
    (@tvpNewCategories dbo.CategoryTableType READONLY)  

Table-Valued 매개 변수를 사용하여 데이터 수정(Transact-SQL)

테이블 반환 매개 변수는 단일 문을 실행하여 여러 행에 영향을 주는 집합 기반 데이터 수정에 사용할 수 있습니다. 예를 들어 테이블 반환 매개 변수의 모든 행을 선택하고 데이터베이스 테이블에 삽입하거나 테이블 반환 매개 변수를 업데이트하려는 테이블에 조인하여 업데이트 문을 만들 수 있습니다.

다음 Transact-SQL UPDATE 문은 카테고리 테이블에 조인하여 테이블 반환 매개 변수를 사용하는 방법을 보여줍니다. FROM 절에서 JOIN과 함께 테이블 반환 매개 변수를 사용하는 경우 다음과 같이 별칭을 지정해야 합니다. 여기서 테이블 반환 매개 변수는 "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를 수행하는 방법을 보여 줍니다.

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

Table-Valued 매개 변수의 제한 사항

테이블 값 매개 변수에는 몇 가지 제한 사항이 있습니다.

• CLR 사용자 정의 함수에는 테이블 반환 매개 변수를 전달할 수 없습니다.

• 테이블 값 매개 변수는 UNIQUE 또는 PRIMARY KEY 제약 조건을 지원하기 위해서만 인덱싱할 수 있습니다. SQL Server는 테이블 반환 매개 변수에 대한 통계를 유지하지 않습니다.

• 테이블 형 매개 변수는 Transact-SQL 코드에서 읽기 전용입니다. 테이블 반환 매개 변수의 행에서 열 값을 업데이트할 수 없으며 행을 삽입하거나 삭제할 수 없습니다. 테이블 반환 매개 변수를 통해 저장 프로시저나 매개 변수가 있는 문에 전달되는 데이터를 수정하려면, 임시 테이블 또는 테이블 변수에 데이터를 삽입해야 합니다.

• ALTER TABLE 문을 사용하여 테이블 반환 매개 변수의 디자인을 수정할 수 없습니다.

SqlParameter 예제 구성

System.Data.SqlClient는 DataTable, DbDataReader 또는 IEnumerable<T> \ SqlDataRecord 개체에서 테이블 반환 매개 변수를 채우는 것을 지원합니다. TypeName 속성의 SqlParameter를 사용하여 테이블 형식 매개 변수의 형식 이름을 지정해야 합니다. 서버에서 이전에 만든 호환 가능한 형식의 이름과 TypeName 이(가) 일치해야 합니다. 다음 코드 조각에서는 데이터를 삽입하도록 구성하는 SqlParameter 방법을 보여 줍니다.

다음 예제에서는 addedCategories 변수가 DataTable을 포함합니다. 변수가 채워지는 방법을 보려면 저장 프로시저에 Table-Valued 매개 변수를 전달하는 다음 섹션의 예제를 참조하세요.

// 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에서 파생된 어떤 개체도 이 조각에 표시된 것처럼 데이터 행을 테이블 값 매개 변수로 스트림하는 데 사용할 수 있습니다.

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

저장 프로시저에 Table-Valued 매개 변수 전달

이 예제에서는 테이블 반환 매개 변수 데이터를 저장 프로시저에 전달하는 방법을 보여 줍니다. 이 코드는 DataTable 메서드를 사용하여 추가된 행을 새 GetChanges로 추출합니다. 코드는 그런 다음 SqlCommand을 정의하고, 속성 CommandType을 StoredProcedure로 설정합니다. SqlParameter는 AddWithValue 메서드를 사용하여 채워지며, SqlDbType는 Structured로 설정됩니다. 그런 다음 메서드 SqlCommand 를 사용하여 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  

매개 변수가 있는 SQL 문에 Table-Valued 매개 변수 전달

다음 예제에서는 dbo에 데이터를 삽입하는 방법을 보여 줍니다. 테이블 반환 매개 변수가 데이터 원본으로 있는 SELECT 하위 쿼리와 함께 INSERT 문을 사용하여 테이블을 범주화합니다. 매개 변수가 있는 SQL 문에 테이블 반환 매개 변수를 전달할 때는 새로운 TypeName 속성을 사용하여 테이블 반환 매개 변수의 형식 이름을 지정해야 합니다. 이는 TypeName 이전에 서버에서 만든 호환되는 형식의 이름과 일치해야 합니다. 이 예제의 코드는 속성 TypeName를 사용하여 dbo.CategoryTableType에 정의된 형식 구조를 참조합니다.

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

DataReader를 사용하여 행 스트리밍

DbDataReader에서 파생된 모든 개체를 사용하여 데이터를 행 단위로 테이블 값 매개 변수에 연속적으로 전송할 수도 있습니다. 다음 코드 조각에서는 OracleCommand 및 OracleDataReader을 사용하여 Oracle 데이터베이스에서 데이터를 검색하는 방법을 보여 줍니다. 그런 다음, 코드는 단일 입력 매개 변수를 사용하여 저장 프로시저를 호출하도록 구성 SqlCommand 합니다. SqlDbType의 SqlParameter 속성은 Structured로 설정됩니다. AddWithValue는 OracleDataReader 결과 집합을 테이블 반환 매개 변수로 저장 프로시저에 전달합니다.

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

참고하십시오

• 매개 변수 및 매개 변수 데이터 형식 구성
• 명령 및 매개 변수
• DataAdapter 매개 변수
• ADO.NET에서의 SQL Server 데이터 작업
• ADO.NET 개요