SQL Server 테이블 반환 매개변수

프로시저를 이용한 집합기반의 처리가 가능할까요?

보통 프로시저 매개변수는 단일한 값으로 처리되기 때문에 다수의 값 처리를 위해서는

루프 형태로 만들어 처리가 완료 될 때 까지 반복적으로 프로시저를 호출합니다.

 

프로시저이기 때문에 매번 실행계획을 세우진 않겠지만

클라이언트와 서버간의 Round Trip은 증가됩니다.

 

하지만 SQL Server 2008 이상부터 추가된 테이블 반환 매개변수를 이용해 집합기반의 처리가 가능합니다.

직원전용 포인트 지급하는 간단한 예를 들어 살펴보도록 하겠습니다. Check this out!

 

1. TestEmpPoint 이란 테이블에 직원이름과 보유한 포인트가 있습니다.

 

2. 프로시저에서 테이블 반환 매개변수를 이용하려면 사용자 테이블 형식을 사용하기 때문에

아래와 같이 만듭니다.

/* 직원 포인트 지급 테이블 형식 만들기*/

CREATE TYPE EmpPointTableType AS TABLE

( EmpName VARCHAR(10)

, Point INT );

GO

 

3. 직원전용 포인트를 지급하는 프로시저를 생성합니다.

CREATE PROCEDURE usp_UpdateEmpPoint

    @Point EmpPointTableType READONLY –위에서 만든 직원 포인트 지급 테이블 형식

    --★테이블 반환 매개변수에 대해선 READOLNY만 가능, DML 작업 불가능

    AS

    SET NOCOUNT ON;

    UPDATE a

           SET a.Point = a.Point + b.Point          

    FROM  dbo.TestEmpPoint a

           INNER JOIN @Point b

           ON a.EmpName = b.EmpName

GO

 

4. 사용자 테이블 형식으로 테이블 변수를 선언하고 지급할 포인트를 입력한 후 프로시저를 호출합니다.

/* 위에서 만든 직원 포인트 지급 테이블 형식으로 테이블 변수 선언*/

DECLARE @UpdatePoint AS EmpPointTableType;

 

/* 테이블 변수에 지급 할 포인트 입력*/

INSERT INTO @UpdatePoint

SELECT '김해', 20000

UNION ALL

SELECT '원미', 10000

UNION ALL

SELECT '백승', 50000

UNION ALL

SELECT '서일', 3000

 

/* 테이블 반환 매개변수로 프로시저 실행 */

EXEC usp_UpdateEmpPoint @UpdatePoint;

GO

 

5. 포인트 지급 확인!

SELECT EmpName, Point

FROM dbo.TestEmpPoint

----------------------------------

 

위와 같이 정상적으로 처리된 것을 확인 가능합니다.

만약 1,000명의 직원이 존재했다면 프로시저를 1,000번 호출했을 텐데

테이블 반환 매개변수를 이용하면 한번으로 줄일 수 있습니다.

 

끝~

 

자세한 사항은 아래 URL 참고해 주세요~

http://technet.microsoft.com/ko-kr/library/bb510489(v=sql.105).aspx