[SQL] 동적쿼리를 사용해보자 (feat.SP_EXECUTESQL)

 

정적쿼리만 하다가 동적쿼리를 하면 조금 복잡하고 어려울 수 있다.

그러면 왜 동적쿼리를 사용해야 되냐?

그 이유는 예외처리를 위해 WHERE 절에 들어가야는 조건절을 손쉽게 제어하기 위함이다.

천천히 따라해보자.

 

예제

테이블 생성

CREATE TABLE #MEMBER(
   num      INT,
   name     NVARCHAR(20),
    score    INT
)
 
INSERT INTO #MEMBER (num, name, score) VALUES(1, '홍길동', 90);
INSERT INTO #MEMBER (num, name, score) VALUES(2, '고길동', 85);
INSERT INTO #MEMBER (num, name, score) VALUES(3, '피카츄', 83);
INSERT INTO #MEMBER (num, name, score) VALUES(4, '파이리', 85);
INSERT INTO #MEMBER (num, name, score) VALUES(5, '꼬북이', 80);

테이블 예제

동적쿼리 생성 과정

-- 매개 변수들을 선언해준다.
DECLARE @sql     AS NVARCHAR(MAX),   -- SQL 구문
       @sqlp    AS NVARCHAR(MAX),   -- SQL 매개 변수
       @num     AS INT,             -- num 매개 변수
        @name    AS NVARCHAR(20),    -- name 매개 변수
       @score   AS INT              -- score 매개 변수
 
-- WHERE 절에 들어갈 변수
SET @score = 85;
 
-- 기본적인 SELECT 구문
SET @sql = N'SELECT * FROM #MEMBER WHERE 1=1 ';
 
-- IF 절로 예외처리
IF ISNULL(@num, 0) > 0 BEGIN
    SET @sql = @sql + ' AND num = @num ';
END
IF ISNULL(@name, '') != '' BEGIN
    SET @sql = @sql + N' AND name = ''' + @name + N'''';
END
IF ISNULL(@score, 0) > 0 BEGIN
    SET @sql = @sql + ' AND score = @score';
END
 
-- EXECUTESQL에 들어갈 매개변수 정의
SET @sqlp = N'
    @num     AS INT,
    @name    AS NVARCHAR(20),
    @score   AS INT';
 
-- 실행
EXEC SP_EXECUTESQL @sql, @sqlp, @num=@num, @name=@name, @score=@score;

 

 

Input Parameter 값의 유무에 따라서 WHERE 조건절에 들어갈지 말아야할지 정할때 동적쿼리를 가장 많이 사용한다.

@num/@name/@score 값의 유무를 @sql에 넣어준다.

 

@sql 변수에 들어간 동적쿼리

@sql 변수의 값이 자유자재로 변하게 된다.

원하는 결과값이 출력

정리

이전 회사에서 동적쿼리가 좋은지?

정적쿼리가 좋은지? 에 대한 갑론을박이 있었다.

사실 동적쿼리의 장점은 속도라고 말할 수 있었는데, 진행하고 있던 프로젝트의 전체적인 프로시저를 동적쿼리에서 정적쿼리로 바꾼적이 있다.

그 이유는 실행계획에 이유가 있었는데...정확한 이유는 기억 못하겠다.

여튼, 동적쿼리든 정적쿼리든 상황에 맞게 잘 사용해야겠다. 개발에는 100%라는 것이 없기에...