테이블의 특정 행의 모든 데이터를 프로시저에 넣어야 할 때가 있습니다.
이 포스팅은 하나의 컬럼당 하나의 변수로 일일이 담기에는 귀찮고
테이블 자체를 통으로 넘겨버리고 싶을 때의 방법입니다.
테이블 형식의 타입 정의
프로시저에 넘겨주는 매개변수와 넘겨받는 매개변수는 테이블 형식을 가져야 합니다.
아래는 간단히 id, name, address 의 컬럼을 가지는 테이블 형식의 타입을 정의하도록 하겠습니다.
-- 테이블 형식 타입 정의
CREATE TYPE testTableType AS TABLE (
id int, -- 컬럼 : id
name nvarchar(50), -- 컬럼 : 이름
address nvarchar(50) -- 컬럼 : 주소
-- 원하는 컬럼 추가
)테이블 자체를 매개변수로 받을 프로시저 정의
위에서 정의한 타입을 이용하여 입력 매개변수를 테이블 형식의 변수로 정의할 수 있습니다.
프로시저의 입력 매개변수 정의
- 변수명 : 입력 매개변수로 사용할 변수의 이름을 정의
- 사용자 정의 타입명 : 사용자가 정의한 타입의 이름
- 이 포스팅에서는 위에서 확인 가능하듯이 'testTableType'을 사용하였습니다.
- READONLY : 테이블 형식의 매개변수에서 사용되는 키워드로 매개변수를 읽기전용으로 설정
- 이 옵션은 테이블 형식으로 전달된 데이터를 조회할 수는 있지만 추가, 수정, 삭제가 불가능합니다.
CREATE PROC testProc (
@tempTable testTableType READONLY
-- 변수명 사용자정의타입명 READONLY
) AS
BEGIN
select * from @tempTable -- 입력 매개변수인 테이블의 값 출력
END프로시저에 넘길 테이블 설정
- 변수의 데이터타입을 정의한 테이블 형식의 타입으로 선언
-- declare 변수명 사용자정의타입명
declare @myType testTableType
- 테이블 변수에 값 입력
- 테이블 형식은 id, name, address가 존재
- 테이블의 값 추가 또한 정해진 형식 내에서 추가 가능
-- 정의한 테이블 형식을 갖는 매개변수에 값 입력 (테이블 변수에 값 추가)
insert into @myType (id, name, address)
values (1, 'eunbyeol', 'incheon')
주의 : 임시 테이블 변수이므로 한 쿼리에서 실행되는 동안에만 유효합니다.
즉, 한 줄씩 실행할 경우 SELECT로 값을 조회할 수 없습니다.
프로시저 입력 매개변수에 테이블 값 전달
- exec 명령어를 사용하여 기존의 입력 매개변수를 넣는 쿼리와 동일
-- exec 프로시저명 입력매개변수
exec testProc @myType
- 실행 결과
전체 쿼리
-- 테이블 형식 타입 정의
CREATE TYPE testTableType AS TABLE (
id int, -- 컬럼 : id
name nvarchar(50), -- 컬럼 : 이름
address nvarchar(50) -- 컬럼 : 주소
-- 원하는 컬럼 추가
)
-- 프로시저 정의
CREATE PROC testProc (
@tempTable testTableType READONLY
-- 변수명 사용자정의타입이름 READONLY
) AS
BEGIN
select * from @tempTable -- 입력 매개변수인 테이블의 값 출력
END
-- 프로시저에 넘길 임시 테이블 변수 정의
declare @myType testTableType
-- 임시 테이블 변수에 값 입력
insert into @myType (id, name, address)
values (1, 'eunbyeol', 'incheon')
-- 프로시저에 테이블 값 매개변수로 입력 후 실행
exec testProc @myType