Pass string into SQL WHERE IN

Question

I am working on a query page where a user selects a value which represents different types, each identified by an ID. The problem is selecting these IDs from the data base using the WHERE IN method.

This is my SQL statement

SELECT M.REG_NO, T.TYPE_ID 
    FROM MAIN AS M 
        INNER JOIN CLASSIFICATION AS C 
            ON M.REG_NO = C.REG_NO
        INNER JOIN TYPE AS T 
            ON T.TYPE_ID = C.TYPE_ID
    WHERE T.TYPE_ID IN (@Types)

it will work for one single value, eg. 46, but NOT if the value is in brackets, eg. (46) or ('46'), the way it should be for the IN.

I am using visual studio which is auto generating the method to access the table adapter to get the values so I think I HAVE to do this through SQL.

I am passing a string, eg. Types = "46,267,2010" , into the adapter method which passes the string into the @Types in the SQL statement.

Any help would be great. Thanks!

Answer 1

It's because you need to have a set of values separated by commas. Like

WHERE
T.TYPE_ID IN ('46', '45', '44');

Are you usign SQL Server or Oracle, MySQL?

If you have a string composed of your multiple values such as

T.TYPE_ID IN ('46,45,44');

You might need a function that will explode your string usign a delimiter (split or whatever it is called depending on you DBMS)

Answer 2

This is a pretty common problem -- not sure why TSQL hasn't dealt with it yet. Anyway, the solution I've found works best for me is to convert the variable to a table, and then you can use IN() on it just fine.

Starting with the function:

CREATE Function [dbo].[ParseStringList]  (@StringArray nvarchar(max) )  
Returns @tbl_string Table  (ParsedString nvarchar(max))  As  

BEGIN 

DECLARE @end Int,
        @start Int

SET @stringArray =  @StringArray + ',' 
SET @start=1
SET @end=1

WHILE @end<Len(@StringArray)
    BEGIN
        SET @end = CharIndex(',', @StringArray, @end)
        INSERT INTO @tbl_string 
            SELECT
                Substring(@StringArray, @start, @end-@start)

        SET @start=@end+1
        SET @end = @end+1
    END

RETURN
END

And then to use the function...

SELECT * 
FROM table
WHERE SomeFieldValue In (Select ParsedString From dbo.ParseStringList(@Types))

Answer 3

The following Select working fine for me:

 SELECT M.REG_NO, T.TYPE_ID 
        FROM MAIN AS M 
            INNER JOIN CLASSIFICATION AS C 
                ON M.REG_NO = C.REG_NO
            INNER JOIN TYPE AS T 
                ON T.TYPE_ID = C.TYPE_ID
        WHERE (','+@Types+',') LIKE '%,' +T.TYPE_ID+ ',%'