동적쿼리

출처:  http://symplog.tistory.com/363 


어쩔 수 없이 동적 쿼리를 사용하다가 보면 가끔씩 아무 생각없이 넘어가는 부분이 많이 있습니다. 
이런 몇가지 부분을 고민해서 해결한다면 좋은 성능과 유지보수 용이성을 가진 쿼리를 만들 수 있습니다. 

1.  SQL Injection 

 - 동적 쿼리를 만들다 보면 흔히 고민하게 되는 부분입니다. 명확한 파라미터 타입 사용, char 타입의 사용시에는 꼭 필요한 길이만, 그리고, 마지막으로 변수값에 대한 필터링이 필요하겠습니다. 

2.  Like 문
 - 보통 Like 를 사용할때 문장을 연결하는 경우가 있습니다.
 

 set @strSQL = ' select top 10 * from mt_song_tmp where song_name like ''' + @likeParam + '%'' '

   이렇게 바꿀 수 있습니다. 

   set @strSQL = ' select top 10 * from mt_song_tmp where song_name like @likeParam ' <-- sp_executesql 을 사용해야 합니다.

   위의 두 쿼리는 파라미터 값만 변경하면 됩니다. 첫번 것은 보통 파라미터 값이 '물건' 이렇게 들어온다면 수정한 문장에서 사용되어 지는 파라미터 값은 '물건%' 라고 하면 되겠지요.

3. 디버깅
 - 동적쿼리는 디버깅 하기가 좀 귀찮은 면이 있습니다. 그렇지만 간단한 파라미터 하나로 디버깅을 할 수 있도록 하면 유지보수성이 좋아집니다. #debug 같은 느낌이랄까요.

  create proc dbo.UP_GetProductInfo
    @likeParam varchar(100),
    @debug bit = 0
  as 
  .......

 if @debug = 1
   print @likeParam -- or 실행될 문장
 else
   exec sp_executesql -- 직접 실행 

4. 유지보수
 - 가끔씩 어떤 테이블을 변경하면서 관련된 쿼리를 찾아야 하는 경우가 발생합니다. 이럴때는 sp_depends 를 사용하게 되는데요. 동적쿼리를 사용하다보면 이 녀석들은 안나타나게 됩니다. 특히나 테이블명을 동적으로 변경하는 쿼리들은 알수가 없게 됩니다. (DMV 쿼리 구문으로도 찾기 어려운 경우가 있습니다.) 
이럴때를 대비해서 아래와 같이 가볍게(?) 하나 넣어주게 되면 향후 유지보수성이 좋아지게 됩니다.

if 1 = 2
begin
  select top(0) 0 from mt_song_tmp;
end 

이상 몇가지를 정리해봤습니다. 

동적쿼리는 구현해야하는 동기 만큼이나 귀찮은 녀석입니다. 조금만 더 신경쓴다면 시스템을 장애로 밀어넣는 훌륭한(?) 품질의 쿼리를 생산하지 않으리라 생각됩니다. 향후 유지보수에도 큰 도움이 될 것입니다.

대략적인 쿼리의 모습은 다음과 같습니다.

create proc dbo.UP_GetSongINFO

           @likeParam varchar(100),

           @debug bit = 0

as

 

set nocount on

 

declare @strSQL nvarchar(2000)

declare @paramDefinition nvarchar(1000)

 

set @strSQL = 'select * from mt_song_tmp where song_nm like @likeParam'

 

set @paramDefinition = '@likeParam varchar(100)'

 

if @debug = 1

begin

           print @likeParam

end

else

           exec sp_executesql @strSQL, @paramDefinition, @likeParam

 

 

if 1 = 2

begin

           select top (0) 0 from mt_song_tmp with (nolock)

end