MSSQL - 프로시저에 테이블 자체를 매개변수로 전달하기

 
테이블의 특정 행의 모든 데이터를 프로시저에 넣어야 할 때가 있습니다.
이 포스팅은 하나의 컬럼당 하나의 변수로 일일이 담기에는 귀찮고
테이블 자체를 통으로 넘겨버리고 싶을 때의 방법입니다.

---

테이블 형식의 타입 정의

프로시저에 넘겨주는 매개변수와 넘겨받는 매개변수는 테이블 형식을 가져야 합니다.
아래는 간단히 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