테이블 반환 매개 변수

  • 아티클

ADO.NET 다운로드

테이블 반환 매개 변수는 클라이언트 애플리케이션에서 SQL Server로 여러 데이터 행을 마샬링하는 간편한 방법을 제공합니다. 데이터 처리를 위해 여러 번의 왕복이나 특수한 서버 측 논리가 필요하지 않습니다. 또한 테이블 반환 매개 변수를 사용하면 클라이언트 애플리케이션에서 데이터 행을 캡슐화하고 매개 변수가 있는 단일 명령으로 데이터를 서버에 보낼 수 있습니다. 들어오는 데이터 행을 테이블 변수에 저장한 다음 Transact-SQL을 사용하여 연산할 수 있습니다.

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

참고

테이블 반환 매개 변수에는 데이터를 반환할 수 없습니다. 테이블 반환 매개 변수는 입력 전용입니다. OUTPUT 키워드는 지원되지 않습니다.

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

리소스 설명
테이블 반환 매개 변수 사용(데이터베이스 엔진) 테이블 반환 매개 변수를 만들고 사용하는 방법을 설명합니다.
사용자 정의 테이블 형식 만들기 테이블 반환 매개 변수를 선언하는 데 사용되는 사용자 정의 테이블 형식에 대해 설명합니다.
사용자 정의 테이블 형식 테이블 반환 매개 변수를 선언하는 데 사용되는 사용자 정의 테이블 형식에 대해 설명합니다.

이전 버전의 SQL Server에서 여러 행 전달

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

  • 일련의 개별 매개 변수를 사용하여 여러 열과 데이터 행의 값을 나타냅니다. 이 방법을 사용하여 전달할 수 있는 데이터의 양은 허용되는 매개 변수의 개수에 의해 제한됩니다. SQL Server 프로시저에는 매개 변수를 2100개까지 사용할 수 있습니다. 이러한 개별 값을 테이블 변수 또는 처리를 위한 임시 테이블로 조합하는 데 서버 쪽 논리가 필요합니다.

  • 여러 데이터 값을 구분된 문자열 또는 XML 문서로 번들로 묶은 다음 해당 텍스트 값을 프로시저 또는 문에 전달합니다. 이 방법은 프로시저 또는 문이 데이터 구조의 유효성을 검사하고 값을 번들에서 해제하기 위한 논리를 포함해야 합니다.

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

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

테이블 반환 매개 변수 형식 만들기

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

다음 문은 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)

테이블 반환 매개 변수를 사용하여 데이터 수정(Transact-SQL)

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

다음 Transact-SQL UPDATE 문은 테이블 반환 매개 변수를 Categories 테이블에 조인하여 사용하는 방법을 보여 줍니다. 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;

테이블 반환 매개 변수의 제한 사항

테이블 반환 매개 변수에는 다음과 같은 몇 가지 제한 사항이 있습니다.

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

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

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

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

SqlParameter 구성 예제

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

다음 예에서 addedCategories 변수는 DataTable을 포함합니다. 변수가 채워지는 방법을 확인하려면 다음 섹션 저장 프로시저에 테이블 반환 매개 변수 전달의 예제를 참조하세요.

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

다음 코드 조각에서 볼 수 있는 것처럼 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;

저장 프로시저에 테이블 반환 매개 변수 전달

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

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

매개 변수가 있는 SQL 문에 테이블 반환 매개 변수 전달

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

참고

테이블 반환 매개 변수에서 ID 열에 대한 값을 제공하는 경우 세션에 SET IDENTITY_INSERT 문을 실행해야 합니다.

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

DataReader를 사용하여 행 스트리밍

DbDataReader에서 파생된 개체를 사용하여 데이터 행을 테이블 반환 매개 변수로 스트림할 수도 있습니다. 다음 코드 조각에서는 OracleCommand 및 OracleDataReader를 사용하여 Oracle 데이터베이스에서 데이터를 검색하는 방법을 보여 줍니다. 그런 다음 코드에서는 단일 입력 매개 변수를 사용하여 저장 프로시저를 호출하도록 SqlCommand를 구성합니다. SqlParameter의 SqlDbType 속성이 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();

다음 단계