[MSSQL]동적 쿼리에서 임시 테이블, 전역 임시 테이블 사용 방법

서론

 

클라이언트가 보내는 변수 값에 따라 쿼리를 동적으로 생성해서 집계해야 하는 요구사항이 있었습니다. 예를 들어 클라이언트가 품목에 대한 수량 집계를 요청하면 품목에 대한 집계 쿼리를, 창고에 대한 수량 집계를 요청하면 이에 대한 집계 쿼리를 동적으로 실행해야 했습니다. 또한 세로로 된 데이터를 피벗을 사용하여 가로로 변환해야 했고 변환된 가로 컬럼들을 클라이언트에 전달해야 했으므로 임시 테이블을 사용하여 데이터를 전달하고자 했습니다. 이 과정 속에서 알게 된 동적 쿼리에서 임시 테이블, 전역 임시 테이블을 사용하는 방법을 소개해보고자 합니다.

 

---

본론

 

1.동적쿼리에서 임시 테이블 사용

 

만약 아래와 같이 품목, 창고에 대한 수량 정보를 가진 임시 테이블이 있다고 가정해봅시다.

 

[#STORAGE_ITEM_TABLE]

 

클라이언트가 보낸 요청 변수가 @GROUP_FLG이고 'I'일 때 품목, 'S'일 때 창고에 대한 수량 집계 쿼리를 실행한다고 하면 아래와 같이 임시 테이블을 사용하여 동적 쿼리를 작성할 수 있습니다. 임시 테이블은 테이블명 앞에 #을 붙여서 사용할 수 있습니다.

 

[임시 테이블을 사용한 동적 쿼리 실행 예제]

IF OBJECT_ID('tempdb..#TEMP_SUM_TABLE') 
   IS NOT NULL DROP TABLE #TEMP_SUM_TABLE
IF OBJECT_ID('tempdb..#STORAGE_ITEM_TABLE') 
   IS NOT NULL DROP TABLE #STORAGE_ITEM_TABLE

DECLARE @SQL NVARCHAR(MAX)
DECLARE @PARAM NVARCHAR(MAX)
DECLARE @GROUP_FLG NVARCHAR(01)--'I' : 품목, 'S' : 창고
DECLARE @DYNAMIC_COL NVARCHAR(50)

CREATE TABLE #STORAGE_ITEM_TABLE(
	ITEM_CODE NVARCHAR(50),
	STORAGE_CODE NVARCHAR(50),
	QTY INT
)

SET @GROUP_FLG = 'I'--품목에 대한 집계 요청
--SET @GROUP_FLG = 'S'--창고에 대한 집계 요청

INSERT INTO #STORAGE_ITEM_TABLE--테스트 데이터 생성
SELECT
	'ITEM0001'
	,'STORAGE1'
	,10
UNION ALL
SELECT
	'ITEM0002'
	,'STORAGE2'
	,20
UNION ALL
SELECT
	'ITEM0003'
	,'STORAGE3'
	,30
UNION ALL
SELECT
	'ITEM0004'
	,'STORAGE2'
	,40
UNION ALL
SELECT
	'ITEM0005'
	,'STORAGE3'
	,50

SET @DYNAMIC_COL = CASE WHEN @GROUP_FLG = 'I' THEN N'ITEM_CODE'
				        WHEN @GROUP_FLG = 'S' THEN N'STORAGE_CODE' END

SET @SQL = N'CREATE TABLE #TEMP_SUM_TABLE( '--동적 쿼리 내부에서 #TEMP_SUM_TABLE 임시 테이블 생성
SET @SQL += @DYNAMIC_COL + N' NVARCHAR(50) ' + N','
SET @SQL += N'QTY INT ) '
SET @SQL += N'INSERT INTO #TEMP_SUM_TABLE '
SET @SQL += N'SELECT ' + @DYNAMIC_COL + ','
SET @SQL += N'SUM(QTY) AS QTY FROM #STORAGE_ITEM_TABLE GROUP BY '+@DYNAMIC_COL
SET @SQL += N' SELECT '+@DYNAMIC_COL+','+'QTY FROM #TEMP_SUM_TABLE'

EXEC SP_EXECUTESQL @SQL

 

[실행결과]

 

@GROUP_FLG 가 'I' 품목인 경우

 

 

@GROUP_FLG 가 'S' 창고인 경우

 

 

2.동적쿼리에서 전역 임시 테이블 사용

 

그런데 만약 동적 쿼리를 실행한 이후에도 동적 쿼리 내부에서 생성한 임시 테이블을 사용하기 위해선 전역 임시 테이블을 사용해야 합니다.  만약 아래와 같이 동적 쿼리에서 생성한 임시 테이블(#TEMP_SUM_TABLE)을 외부에서 조회하면

 

IF OBJECT_ID('tempdb..#TEMP_SUM_TABLE') 
   IS NOT NULL DROP TABLE #TEMP_SUM_TABLE
IF OBJECT_ID('tempdb..#STORAGE_ITEM_TABLE') 
   IS NOT NULL DROP TABLE #STORAGE_ITEM_TABLE

DECLARE @SQL NVARCHAR(MAX)
DECLARE @PARAM NVARCHAR(MAX)
DECLARE @GROUP_FLG NVARCHAR(01)--'I' : 품목, 'S' : 창고
DECLARE @DYNAMIC_COL NVARCHAR(50)

CREATE TABLE #STORAGE_ITEM_TABLE(
	ITEM_CODE NVARCHAR(50),
	STORAGE_CODE NVARCHAR(50),
	QTY INT
)

SET @GROUP_FLG = 'I'--품목에 대한 집계 요청
--SET @GROUP_FLG = 'S'--창고에 대한 집계 요청

INSERT INTO #STORAGE_ITEM_TABLE--테스트 데이터 생성
SELECT
	'ITEM0001'
	,'STORAGE1'
	,10
UNION ALL
SELECT
	'ITEM0002'
	,'STORAGE2'
	,20
UNION ALL
SELECT
	'ITEM0003'
	,'STORAGE3'
	,30
UNION ALL
SELECT
	'ITEM0004'
	,'STORAGE2'
	,40
UNION ALL
SELECT
	'ITEM0005'
	,'STORAGE3'
	,50

SET @DYNAMIC_COL = CASE WHEN @GROUP_FLG = 'I' THEN N'ITEM_CODE'
				        WHEN @GROUP_FLG = 'S' THEN N'STORAGE_CODE' END

SET @SQL = N'CREATE TABLE #TEMP_SUM_TABLE( '--동적 쿼리 내부에서 #TEMP_SUM_TABLE 임시 테이블 생성
SET @SQL += @DYNAMIC_COL + N' NVARCHAR(50) ' + N','
SET @SQL += N'QTY INT ) '
SET @SQL += N'INSERT INTO #TEMP_SUM_TABLE '
SET @SQL += N'SELECT ' + @DYNAMIC_COL + ','
SET @SQL += N'SUM(QTY) AS QTY FROM #STORAGE_ITEM_TABLE GROUP BY '+@DYNAMIC_COL

EXEC SP_EXECUTESQL @SQL

SELECT* FROM #TEMP_SUM_TABLE--동적 쿼리에서 생성한 임시 테이블을 조회하면 실행 안됨.

 

[에러 메시지]

 

 

위와 같이 임시 테이블 개체가 유효하지 않다는 에러 메시지가 나타납니다. 그 이유는 동적 쿼리는 다른 세션에서 실행한 결과를 리턴 받는 형식이기 때문에 세션이 끝나면 삭제되는 임시 테이블을 조회할 수 없게 됩니다. 따라서 이러한 경우 모든 세션이 공유하는 전역 임시 테이블을 생성하여 문제를 해결할 수 있습니다. 전역 임시 테이블은 테이블명 앞에 ## 을 붙여서 사용할 수 있습니다.

 

[전역 임시 테이블을 사용한 동적 쿼리 실행 예제]

IF OBJECT_ID('tempdb..##TEMP_SUM_TABLE') 
   IS NOT NULL DROP TABLE ##TEMP_SUM_TABLE
IF OBJECT_ID('tempdb..#STORAGE_ITEM_TABLE') 
   IS NOT NULL DROP TABLE #STORAGE_ITEM_TABLE

DECLARE @SQL NVARCHAR(MAX)
DECLARE @PARAM NVARCHAR(MAX)
DECLARE @GROUP_FLG NVARCHAR(01)--'I' : 품목, 'S' : 창고
DECLARE @DYNAMIC_COL NVARCHAR(50)

CREATE TABLE #STORAGE_ITEM_TABLE(
	ITEM_CODE NVARCHAR(50),
	STORAGE_CODE NVARCHAR(50),
	QTY INT
)

SET @GROUP_FLG = 'I'--품목에 대한 집계 요청
--SET @GROUP_FLG = 'S'--창고에 대한 집계 요청

INSERT INTO #STORAGE_ITEM_TABLE
SELECT
	'ITEM0001'
	,'STORAGE1'
	,10
UNION ALL
SELECT
	'ITEM0002'
	,'STORAGE2'
	,20
UNION ALL
SELECT
	'ITEM0003'
	,'STORAGE3'
	,30
UNION ALL
SELECT
	'ITEM0004'
	,'STORAGE2'
	,40
UNION ALL
SELECT
	'ITEM0005'
	,'STORAGE3'
	,50

SET @DYNAMIC_COL = CASE WHEN @GROUP_FLG = 'I' THEN N'ITEM_CODE'
				        WHEN @GROUP_FLG = 'S' THEN N'STORAGE_CODE' END

SET @SQL = N'CREATE TABLE ##TEMP_SUM_TABLE( '--전역 임시 테이블 생성
SET @SQL += @DYNAMIC_COL + N' NVARCHAR(50) ' + N','
SET @SQL += N'QTY INT ) '
SET @SQL += N'INSERT INTO ##TEMP_SUM_TABLE '
SET @SQL += N'SELECT ' + @DYNAMIC_COL + ','
SET @SQL += N'SUM(QTY) AS QTY FROM #STORAGE_ITEM_TABLE GROUP BY '+@DYNAMIC_COL

EXEC SP_EXECUTESQL @SQL

SELECT* FROM ##TEMP_SUM_TABLE--전역 임시 테이블 조회, 실행해도 문제가 발생하지 않음

 

전역 임시 테이블의 경우 모든 세션에서 사용되지 않아야 삭제가 되기 때문에 동적 쿼리에서 생성해도 사용이 가능합니다. 

 

3. 전역 임시 테이블 사용 시 주의할 점

 

전역 임시 테이블은 모든 세션에서 공유하기 때문에 동시에 사용할 경우 교착 상태가 발생할 수 있습니다. 따라서 이러한 상황을 인지하고 문제가 발생할 경우는 없는지 검토할 필요가 있습니다.

 

---

결론

 

동적 쿼리로 임시 테이블을 사용해야 할 일이 있었던 계기로 내용을 정리해보았습니다. 평소에 동적 쿼리로 작성해야 할 일이 그렇게 많지 않았었는데 동적 쿼리 사용법을 다시 한번 정리하고 동적 쿼리 내에서 임시 테이블을 사용해본 좋은 경험이었습니다!