MSSQL Dynamic Query가 아닌 Static Query시 in 쿼리 사용하는 방법
A라는 테이블이 있고 idx라는 필드가 있다 치자
레코드는 1부터 5까지 총5개가 있을 경우
일반적으로 다이나믹 쿼리를 날리면
dim idxs
idxs = "1,2,3,4,5"
strQuery = "select idx from a where idx in ( " & idxs & ")"
이렇게 쉽다.
하지만 스태틱 쿼리로 날리려 들면 답이 없다.
dim idxs
idxs = "1,2,3,4,5"
strQuery = "select idx from a where idx in (?)"
With com
.ActiveConnection = dbConn
.Parameters.Append .CreateParameter ("@IDX", advarchar, adParamInput, 1000, idxs)
.CommandText = strQuery
rs.CursorLocation = adUseClient
rs.Open com, , adOpenStatic, adLockReadOnly
End With
이렇게 만들어봐야 동작이 되지 않는다.
방법은 스토어드프로시져를 만들어도 되지만 개인적으로 stored procedure는 소스 관리상 번거롭고 거북스럽다.
이런거 하나 하나 마다 스토어드 프로시저를 만들다면 정신이 혼미해진다.
방법은 아래와 같다.
함수를 만들어서 1,2,3,4,5를 1 2 3 4 5로 바꿔주는 함수를 만들면 된다.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[SplitString]
(
@List NVARCHAR(MAX),
@Delim VARCHAR(255)
)
RETURNS TABLE
AS
RETURN ( SELECT [Value] FROM
(
SELECT
[Value] = LTRIM(RTRIM(SUBSTRING(@List, [Number],
CHARINDEX(@Delim, @List + @Delim, [Number]) - [Number])))
FROM (SELECT Number = ROW_NUMBER() OVER (ORDER BY name)
FROM sys.all_objects) AS x
WHERE Number <= LEN(@List)
AND SUBSTRING(@Delim + @List, [Number], LEN(@Delim)) = @Delim
) AS y
);
GO
그리고 static query에선 아래와 같이 사용하면 잘 스태틱 쿼리에서도 in query를 사용할 수 있음.
dim idxs
idxs = "1,2,3,4,5"
strQuery = "select idx from a where idx in ( select * from SplitString (?, ',') )"
With com
.ActiveConnection = dbConn
.Parameters.Append .CreateParameter ("@IDX", advarchar, adParamInput, 1000, idxs)
.CommandText = strQuery
rs.CursorLocation = adUseClient
rs.Open com, , adOpenStatic, adLockReadOnly
End With
