SQL Server - 테이블 반환 매개변수 Table Valued Parameters in SQL Server

테이블 반환이란 새로운 기능은 2008년에 소개되었다. 테이블 반환 매개변수는 테이블(여러개의 데이터 행을 가지고 있는)이 어플리케이션 또는 T-SQL에서 부터 저장 프로시저에 매개변수로 전달 될 수 있다. 2008년 전에는 매개변수로 테이블을 전달할 수 없었다.

Table-Valued Parameter is a new feature introduced in SQL Server 2008. Table-Valued Parameter allows a table(i.e multiple rows of data) to be passed as a parameter to a stored procedure from T-SQL code or from an application. Prior to SQL Server 2008, it is not possible to pass a table variable to a stored procedure.

 

참고 : 테이블 반환 매개변수는 반드시 읽기전용으로 프로시저나 다른 함수에 전달되어야 한다. 이 뜻은 DML 작업을 할 수 없다는 뜻이다.

Please note : Table-Valued parameters must be passed as read-only to stored procedures, function etc. This means you cannot perform DML operations like INSERT, UPDATE or DELETE on a table-valued parameter in the body of a function, stored procedure etc.

 

 

여러개의 행을 테이블 반환 매개변수 사용하여 프로시저에 전달하는 3단계

3 steps to pass multiple rows to a stored procedure using Table-Valued Parameter.

1. Create User-defined Table Type

2. Use the User-defined Table Type as a parameter in the stored procedure.

3. Declare a table variable, insert the data rows and then pass the table variable as a parameter to the stored procedure.

 

--1. 사용자 정의 테이블 타입을 만듬 Create User-defined Table Type
Create Type EmpTableType as Table
(
	Id int primary key,
	Name nvarchar(50),
	Gender nvarchar(10)
)

--2. 사용자 정의 테이블을 저장 프로시저의 매개변수로 사용한다.
Create Proc spInsertEmployees
@EmpTableType EmpTableType READONLY
AS
BEGIN
Insert Into Employees
Select * from @EmpTableType
END

--3. 테이블 변수를 선언하고 테이블 변수에 들어갈 데이터들을 프로시저에 보낸다음 테이블변수를 프로시저 매개변수로 넣어준다.
--Declare the Table Variable
Declare @EmployeeTableType EmpTableType

--Insert rows into the Table variable that you want to send to the SP
Insert into @EmployeeTableType values(1, 'Mark', 'Male')
Insert into @EmployeeTableType values(2, 'Mary', 'Female')
Insert into @EmployeeTableType values(3, 'John', 'Male')
Insert into @EmployeeTableType values(4, 'Sara', 'Female')
Insert into @EmployeeTableType values(5, 'Rob', 'Male')

--Pass the table variable as a parameter to the stored procedure
Execute spInsertEmployees @EmployeeTableType