[MSSQL/SQL Server] 동적 쿼리

1. 개요

• 동적 쿼리는 프로그램이 실행되는(런타임) 동안 생성되고 실행되는 SQL문이다.
• 동적 쿼리는 SQL 문장을 실행 중에 조립하는 기법으로, 이를 활용해 외부에서 전달한 변수값에 따라 다르게 동작하는 명령문을 생성할 수 있다.
• 동적 쿼리의 핵심은 '쿼리를 변수에 담는다'는 것인데, 동적쿼리를 활용하면 쿼리문을 String으로 만들어 변수화할 수 있기 때문에 길고 복잡한 쿼리를 간단하게 처리할 수 있게 된다.

-- 아래는 쿼리문을 String으로 만들어 변수화하는 예시이다.
DECLARE @yymm nvarchar(6), @sql nvarchar(1000)                

SET @yymm = (SELECT convert(char(6), getdate(), 112))
SET @sql = N'SELECT @count = count(*) FROM history_' + @yymm

 

2. 동적 쿼리와 정적 쿼리 비교

동적 쿼리

• 정의: 실행되는(런타임) 동안 생성되고 실행되는 SQL문
• 장점: 정적 SQL보다 쿼리의 유연성이 높고, 더 넓은 범위의 조건과 상황을 처리할 수 있음
• 단점: 동적쿼리 사용 시 파라미터 입력에 따른 최적화되지 못한 실행계획으로 인한 성능 저하를 일으킬 수 있으므로 유의해야 함

정적 쿼리

• 고정된 방식으로 작성되고 실행되는 SQL 문으로, SQL문은 프로그램이 실행되기 전에 정의됨
• 데이터나 다른 변수의 변화에 영향을 받지 않음
  
• 장점: db에 의해 최적화되고 사전 컴파일될 수 있음

 

3. 동적 쿼리의 실행

동적 쿼리를 실행하는 방법은 두 가지가 있다.

 

• EXEC (@sql)
• EXEC sp_executesql

 

3-1. EXEC와 EXEC sp_executesql 비교

 

① 활용도 측면

• EXEC는 쿼리를 실행시켜 주지만, 결과에 대한 리턴을 받아서 활용할 수 없다.
• 반면에 sp_executesql은 매개변수로 값을 넘길 수 있고, 받아올 수도 있기 때문에 활용도가 높다. (즉 매개변수 입력뿐만 아니라 출력도 정의할 수 있다.)

② 캐시 재사용 측면

• EXEC의 경우, 매개변수를 String화된 쿼리문 안에 넣을 수 없어, 매개변수 값이 바뀔 때마다 재컴파일되고 새로 캐싱된다.
• 반면 sp_executesql의 경우, 매개변수를 파라미터화하기 때문에 쿼리문 자체의 변경이 없다면 캐싱된 실행계획을 재사용할 수 있다. (즉 쿼리문이 고정된 채 매개변수 값만 바뀌는 경우라면, 실행계획을 캐싱, 재사용할 수 있다.)

 

3-2. sp_executesql의 기본 구문

EXEC sp_executesql [ @stmt = ] STATEMENT
[
 { , [@params = ] N'@Parameter_name data_type [ OUT | OUTPUT ] [ , ...n]' }
 { , [@param1 = ] 'value1' [ ,...n] }
]

 

• sp_executesql의 인수는 [@stmt, @params, @param1]이 있다.
• @stmt는 문자열로 된 SQL문이다.
• [@params=] N’@Parameter_name data_type [ OUT | OUTPUT ] [ , ...n]'는 @stmt에 포함된 모든 매개변수를 정의하는 하나의 문자열이다.
• [@param1 =] ‘value1’은 매개변수 문자열에 정의된 첫 번째 매개변수의 값이다.
• [OUT | OUTPUT]은 매개변수가 출력 매개변수임을 나타낸다.
• 주의할 점: sp_executesql는 varchar가 아닌 nvarchar를 요구한다.

 

4. 동적 쿼리 실습

유저등급(user_grade) 테이블에서 유저번호(ug_uicode)가 48인 유저를 조회하자.

실행계획 재사용을 위해 sp_executesql로 동적 쿼리를 실행한다.

declare @sql nvarchar(1000)
declare @params nvarchar(20)

set @sql = N'select user_name, grade from user_grade where ug_uicode=@userno'	-- 실행할 SQL문 정의
set @params = N'@userno int output'
 
exec sp_executesql @sql, @params, @userno=48

 

▶ 쿼리 결과

(생략)

 

▶ 실행계획 재사용 여부 확인

위 쿼리의 실행계획 재사용 여부를 확인하기 위해, 시스템 카탈로그를 확인해본다.

-- 시스템 카탈로그 확인
SELECT sql, cacheobjtype, objtype, usecounts, pagesused
FROM SYS.SYSCACHEOBJECTS with(nolock)
WHERE dbid = DB_ID('DATABASENAME')
  and sql like '%select user_name, grade from user_grade where ug_uicode=@userno'
  and sql like '%output%'

 

 

→ 확인 결과, sp_executesql 방식은 Prepared으로 objtype에 등록되었으며, use count가 12이므로 모든 매개변수 값에 대해 실행계획을 재사용함을 알 수 있다.