[MSSQL] SP_EXECUTESQL로 파라미터를 이용해 동적 쿼리로 테이블 생성 시 이슈사항 및 해결방법

서론

 

SP_EXECUTESQL로 파라미터를 사용하여 동적 쿼리를 작성하여 테이블을 생성하려고 했으나 테이블이 제대로 생성되지 않았습니다. 그 원인을 파악해보니 파라미터로 넘겨지는 문자열은 실제 파라미터로 전달될 때 앞뒤로 작은따옴표 ' 가 붙는다는 걸 인지하지 못하고 쿼리를 작성한 것이 이유였습니다. 제가 겪었던 어려움을 토대로 SP_EXECUTESQL을 통해 파라미터로 동적 쿼리를 실행 시 생길 수 있는 이슈사항에 대해 이야기해볼까 합니다.

 

---

본론

 

저는 클라이언트에서 전달한 값에 따라 컬럼 값을 다르게 갖는 임시 테이블을 동적 쿼리 내에서 생성하고 싶었습니다. 예를 들어 클라이언트가 전달한 값이 'I'면 ITEM_CODE, 'S'면 STORAGE_CODE 컬럼을 갖고 QTY 컬럼을 갖는 임시 테이블을 생성하고 싶었습니다. 그래서 아래와 같이 SQL을 작성하여 SP_EXECUTESQL로 동적 쿼리를 실행할 때 컬럼명들을 파라미터를 전달하여 테이블을 생성하려 했습니다.

 

[처음 작성한 동적 쿼리문]

 

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

SET @GROUP_FLG = 'S'

SET @SQL = N'CREATE TABLE #TEMP_TABLE('
SET @SQL += N'@PARAM1' + N','
SET @SQL += N'QTY INT )'

SET @PARAM = N'@PARAM1 NVARCHAR(50)'
SET @DYNAMIC_COL = CASE WHEN @GROUP_FLG = 'I' THEN N'ITEM_CODE NVARCHAR(50)'
                        WHEN @GROUP_FLG = 'S' THEN N'STORAGE_CODE NVARCHAR(50)' END
EXEC SP_EXECUTESQL @SQL, @PARAM, @PARAM1 = @DYNAMIC_COL

 

[에러 메시지]

 

 

[실제로 실행되는 동적 쿼리문 - 실행 안됨]

 

CREATE TABLE #TEMP_TABLE(
	'ITEM_CODE NVARCHAR(50)',--파라미터로 넘겼으므로 양 옆에 '가 붙게됨. 따라서 실행이 안됨.
	QTY INT
)

처음엔 파라미터에 작은따옴표 ' 가 붙는다는 걸 생각하지 못하고 문법 문제인 줄 알고 엄청 고민했습니다... 그러다 저 방식대로 조회할 컬럼을 SELECT문에 파라미터로 넘기는 동적 조회 쿼리를 작성하고 실행해보자... 실제 데이터가 아닌 넘긴 파라미터로만 데이터가 나오는 이상한 결과를 보고 깨닫게 되었습니다. 따라서 파라미터를 전달하여 테이블 컬럼을 선언하는 방식을 파라미터를 전달하지 않고 문자열 자체 내에서 더하는 방식으로 개선하여 문제를 해결했습니다.

 

[개선한 동적 쿼리문]

 

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

SET @GROUP_FLG = 'S'

SET @SQL = N'CREATE TABLE #TEMP_TABLE('
SET @SQL += CASE WHEN @GROUP_FLG = 'I' THEN N'ITEM_CODE NVARCHAR(50)'
				 WHEN @GROUP_FLG = 'S' THEN N'STORAGE_CODE NVARCHAR(50)' END + ','
SET @SQL += 'QTY INT )'

EXEC SP_EXECUTESQL @SQL

 

[실제로 실행되는 동적 쿼리문 - 실행됨]

 

CREATE TABLE #TEMP_TABLE(
	ITEM_CODE NVARCHAR(50),--문자열을 더하여 생성했으므로 '가 붙지 않음. 따라서 실행됨
	QTY INT
)

 

---

결론

 

보통 조건문에 사용하기 위해 파라미터로 전달되는 문자열은 작은따옴표 '를 양옆에 달고 전달이 되는데 이 부분을 간과하고 동적 쿼리를 작성하여 문제를 겪었습니다. 다음부터 SP_EXECUTESQL를 통해 파라미터를 전달하여 동적 쿼리 실행 시 이와 같은 사항을 숙지하고 잘못된 쿼리를 작성하지 않도록 주의해야겠습니다!