[MSSQL] 동적 쿼리를 사용하여 원하는 컬럼 업데이트 시키기

나는 DB로 mysql을 주로 사용하면서 공부했었는데, 이번에 일하면서 ASP.NET으로 구축된 웹 프로그램 DB가 MS-SQL을 사용하면서 mysql보다는 mssql에 더 익숙해져 갔다. 특히 프로시저를 많이 다뤘는데 프로시저를 짜내는 것이 너무 어려웠다. 그렇지만 아직까지는 큰 문제없이 잘 해내고 있는 것 같다.

 

그러던 중 프로그램 하나를 개발하는데... 웹에서 전달 받은 프로시저를 호출하여 DB를 업데이트시키는데 컬럼의 종류에 따라 DB를 업데이트시킬 필요가 있었다.

 

쉽게 말하면 아래의 테이블 구조처럼 되어있으면 저 컬럼명을 매개 변수로 받고 그 값에 따라 업데이트하고 싶다는 뜻이다.

 

-- @ITEM 값에 ITEM1 이라는 값을 전달하면 ITEM1 컬럼만 업데이트 되도록

@ITEM  VARCHAR(20)

BEGIN
	UPDATE 테이블 SET @ITEM = 값  -- 이런 식으로 매개변수 값을 전달받아 원하는 컬럼에 넣고 싶다
END

 

 

그러나 저렇게 업데이트 하게 되면 오류가 난다... 그래서 나는 다른 방법을 찾기로 했다.

1. 동적 쿼리 사용

찾아보니 동적 쿼리라는 것을 알게 되었다. 동적쿼리는 sql을 문자열로 묶어서 그 문자열을 그대로 실행해 주게 된다. 엑셀에서 아래의 이런 표현식으로 동작하는 방식이 비슷하다고 보면 된다.

엑셀 문자 합치기 표현식

2. 사용 예제

2-1. 테스트 테이블 생성

먼저 잘 동작 하는지 테스트 테이블을 생성해서 만들어 보겠다.

CREATE TABLE TEST
(
	ID INT PRIMARY KEY,
	NAME VARCHAR(20),
	ITEM1 VARCHAR(20),
	ITEM2 VARCHAR(20),
	ITEM3 VARCHAR(20)
)
GO

INSERT INTO TEST (ID, NAME, ITEM1,ITEM2,ITEM3) VALUES (1, 'Joe', 10, 21, 25)
INSERT INTO TEST (ID, NAME, ITEM1,ITEM2,ITEM3) VALUES (2, 'Bot', 31, 85, 96)
INSERT INTO TEST (ID, NAME, ITEM1,ITEM2,ITEM3) VALUES (3, 'Kaite', 24, 36, 21)
INSERT INTO TEST (ID, NAME, ITEM1,ITEM2,ITEM3) VALUES (4, 'Jessy', 74, 43, 23)
INSERT INTO TEST (ID, NAME, ITEM1,ITEM2,ITEM3) VALUES (5, 'Sia', 68, 54, 35)

 

테스트 item번호에 따라 업데이트시키기 위해 이런 식으로 테이블을 생성해 보았다.

생성된 예제 테이블

2-2. 프로시저 생성(동적 쿼리)

이제 동적 쿼리를 작성해보겠다.

동적쿼리는 쿼리 실행을 위한 변수 하나를 만들고 그 쿼리를 다시 실행해 주는 것이다.

먼저 업데이트 구문을 작성하기 전 간단하게 필요한 컬럼을 선택하는 쿼리를 작성해 보겠다.

CREATE PROCEDURE [dbo].[TEST_PROCEDURE]

@ITEM			VARCHAR(20)
AS
DECLARE @SQL NVARCHAR(MAX)

BEGIN
	
	SET @SQL = 'SELECT '+ @ITEM + ' FROM TEST'
	EXEC(@SQL)
END

 

@SQL로 SQL 구문을 넣을 변수를 생성해주고 따옴표로 @ITEM처럼 변수를 제외한 쿼리문을 따옴표로 묶어서 @SQL 변수에 담는다. 그리고 EXEC로 SQL을 실행해 주는 코드를 입력한다.

 

주의할 점은 띄어쓰기를 잘해야 한다. 저렇게 'SELECT' + @ITEM 'FROM TEST' 이렇게 SELECT 뒤와 FROM 앞에 공백이 없으면 출력할 때 "SELECT@ITEMFROM TEST" 이렇게 나오기 때문에 띄어쓰기를 잘해야 한다.

 

이렇게 해서 프로시저를 작성하고 예를 들어 ITEM2라는 컬럼을 출력하고 싶다면...

EXEC TEST_PROCEDURE 'ITEM2'

 

프로시저를 실행했을 때 결과가 이렇게 나온다.

ITEM2 컬럼 출력

 

이제 원하는 컬럼을 업데이트 할 수 있는 프로시저를 만들어 보겠다.

기존의 프로시저를 변경해서 사용해 보도록 하겠다.

ALTER PROCEDURE [dbo].[TEST_PROCEDURE]

@ITEM			VARCHAR(20),
@COUNT			VARCHAR(20)
AS
DECLARE @SQL NVARCHAR(MAX)

BEGIN
	
	SET @SQL = 'UPDATE TEST SET '+ @ITEM + ' = ' + @COUNT
	EXEC(@SQL)
END

 

원하는 @COUNT 매개변수를 추가해 원하는 ITEM 컬럼에 원하는 값을 변경하고 싶을때 이렇게 쿼리를 작성하고 실행해보도록 하겠다. 여기서는 ITEM3 컬럼에 300이라는 값으로 업데이트 해보도록 하겠다.

EXEC TEST_PROCEDURE 'ITEM3', 300

그러면 내가 원하는 컬럼에 이렇게 잘 업데이트된 것이 보인다.

ITEM3에 300값으로 업데이트 한 모습

 

나중에 변수를 다양하게 주고 WHERE 절도 응용해서 사용할 수 있다.

 

2-3. 주의점

그렇지만 이렇게 동적 쿼리를 사용하면 sql 인젝션에 취약해진다. 그러므로 각별히 보안에 더 주의해야 한다.

 

이렇게 내가 원하는 컬럼을 동적쿼리를 사용해서 업데이트까지 해보았다.