MSSQL Dynamic Query가 아닌 Static Query시 in query(in 쿼리) 사용하는 방법

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