[ DB ] MSSQL 동적 쿼리 방법 -exec() 와 sp_executesql

동적쿼리라고 하면 다들 아시긴 할텐데, 

저 초보때는 동적이라는 말에 대해서도 잘 쓰지 않았던 말이라..

필요할 때 테이블명이나, 특정 값을 변형 시키는 거라고 할 수 있습니다.

 

프로젝트를 진행하다 보면 데이터베이스의 성능이나 효율적인 설계를 고려하여 

히스토리성 테이블을 월별로 쪼개는 경우가 있습니다. 

이럴땐 테이블 이름을 history_1701, history_1702 등으로 자동 생성 하는 구조를 쓰게 되는데요. 

히스토리 테이블에 쌓여 있는 현재까지의  레코드가 몇 건 인가를 매일 또는 매주 정기적으로 점검해야 하는 상황등에

유용할 수 있는 방법입니다.

쿼리를 실행하는 날짜의 연도와 워에 맞춰 테이블 네임을 자동으로 지정할 수 있으니까요.

소스를 한번 보시고, 대충 간단하게 설명을 드리도록 하겠습니다.

설명할게 있을런지나 모르겠네요.

 

declare @yymm nvarchar(40)
declare @cntQuery nvarchar(1000)
declare @t_count int

set @yymm = (select right(replace(convert(varchar(7),GETDATE(),121),'-',''),4))
set @cntQuery = N'select @count=count(*) from history_' + @yymm

EXEC sp_executesql @cntQuery, N'@count int output',@count=@t_count OUTPUT
SELECT @cntQuery, @t_count

 

핵심은 cntQuery를 실행시켜 주는 EXEC sp_executesql 입니다.

MSSQL 함수 중에 exec("쿼리") 는 쿼리를 실행시켜 주지만, 결과에 대한 리턴을 받아서 활용을 할 수가 없습니다.

반면에 sp_executesql은 파라미터로 값을 넘길 수도 있고, 받아 올수도 있기 때문에 활용용도가 많습니다.

 

그래서 리턴값을 받기 위해서는 문법이 조금 복잡해 보이지만 사실은 별거 없는 저장 프로시저인 sp_executesql을 이용합니다.

(데이터베이스의 성능을 고려해도 exec() 보다는 sp_executesql를 사용하는 편이 좋습니다. 캐시 재활용면에서.)

 

@cntQuery 안에 @count라는 변수에 count(*) 된 결과를 담겠다는 뜻이고, @count의 값을 @t_count 변수에 출력한다는 뜻입니다.

EXEC sp_executesql @cntQuery, N'@count int output',@count=@t_count OUTPUT

 

보다 자세한 sp_executesql 사용 방법

sp_executesql 사용