MSSQL 동적쿼리 사용

Classic ASP코드를 .NET코드로 변환함과 동시에 기존 쿼리를 프로시져로 변환하는 작업을 하던 중 

방대한 양의 데이터를 조회해야 하는데 정해진 데이터만을 조회하는 것이 아닌 변환 되는 컬럼 값을 조회해야 하는 코드를 작성해야 했다.

 

MSSQL은 문자열로 만들어진 쿼리문을 동작 시켜주는 함수를 가지고 있기 때문에 이것을 활용 한 데이터 조회 프로시져를 만들기로 했다.

 

☝작성

우선 쿼리문을 작성 할 변수를 설정한다.

DECLARE @SQL varchar(max)

 

DECLARE은 변수를 생성 해줄 때 필요한 선언이며

@SQL은 값을 담을 변수이고, 

varchar(max)는 문자열과 그 크기를 지정해주는 값이다.

 

 

SET @SQL = 'SELECT 1 as value'

변수에 값을 담을 때에는 "SET"을 앞에 붙여 주어야 해당 변수에 값이 적용이 된다.

 

EXEC(@SQL)

해당 쿼리문을 실행 할 때에는 EXEC() 함수를 사용하여 문자열이 담겨진 @SQL 변수를 실행하면 쿼리문의 결과를 얻을 수 있다.

 

💁‍♂️오류

작성 중간중간 오류가 발생했는데 주의해야할 사항은 크게 3가지가 있었던 것 같다.

1. 동적 쿼리안에서의 변수 생성

2. 문자열로서 변환해주기 위한 ' 사용

3. 반복문에서의 동적 쿼리 사용

 

👍동적 쿼리안에서의 변수 생성

이 경우는 모든 경우에서 그러한 것인지 모르겠지만, 작업중 문자열을 계속해서 더해 나가는 경우에서 생긴 오류였다.

DECLARE @SQL VARCHAR(MAX),
        @value int

이처럼 변수를 생성 후에 실행하면

set @SQL = 'SELECT @value = 1'
EXEC(@SQL)

 

Must declare the scalar variable "@value".

이처럼 변수를 선언하라는 오류 메시지를 띄운다.

 

이럴 때에는

DECLAR	@SQL VARCHAR(MAX)


SET @SQL = ' DECLARE @value int' 
SET @SQL += ' select @value = 1'
SET @SQL += ' select @value as value '

EXEC(@SQL)

이처럼 변수를 선언하는 것도 문자열에 포함시켜서 해당 변수값을 조회하는 쿼리 전부를 문자열 변수에 담아서 실행하게 되면 원하는 값을 얻을 수 있다.

 

👍문자열로서 변환해주기 위한 ' 사용

가장 헷갈리는 오류 중 하나였다.

일반적으로 프로시져는 외부로 부터 전해 받은 값을 프로시져를 생성할 때에 필요한 변수를 선언하여 그안에 값을 받아 올 수 있는데 이 값을 동적 쿼리 즉 문자열 안에 집어 넣었을 때에는 해당 문자열 변수의 값을 문자열로 읽히지 않는 오류이다.

 

이 경우는

DECLARE @SQL varchar(max), 
		@var1 varchar(20)
        
set @SQL = ' SELECT * FROM USER WHERE id = '''+@var1+ ''''


exec(@SQL)

위처럼 변수를 따옴표 3개씩 묶어주어야 @SQL  변수를 실행할 경우    [  '@var1값'  ]  이러한 형식으로 읽을 수 있다.

 

👍반복문에서의 동적 쿼리 사용

마지막은 간단하지만 쉽게 잊을 수 있는 사항이다.

DECLARE @SQL VARCHAR(MAX)
        @start 	int = 1,
        @end	int = 10
        



WHILE @start <= @end
BEGIN
	set @SQL = ' SELECT * from user where id = '''+ @start + ''''
    exec(@SQL)
	set @start += 1
END

문자열안에 변수를 넣고 양쪽에 '를 3개씩 붙였는데도 오류를 반환한다.

이유는 @start 변수는 int로 선언이 되었기 때문에 문자열 안에서 사용할 수 없다는 것이다.

 

DECLARE @SQL VARCHAR(MAX)
        @start 	int = 1,
        @end	int = 10
        



WHILE @start <= @end
BEGIN
	set @SQL = ' SELECT * from user where id = '+ convert(varchar,@start) + ''
    exec(@SQL)
	set @start += 1
END

위와 같이 convert를 사용해서 숫자를 문자열로 변환시켜 주어 담아주면 정상적으로 작동하게 된다.