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!

Don't know SQL Server, but in Oracle, you can't pass in a bound variable like your @Types - you have to pass in a separate var or constant for each IN value. — Ed Staub, Aug 04 '11 at 16:47

Chains · Accepted Answer · 2011-08-04T17:09:23.450

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))

Sylvain Cloutier · Answer 2 · 2011-08-04T16:51:19.403

5

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)

edited Aug 04 '11 at 16:51

answered Aug 04 '11 at 16:45

Sylvain Cloutier

368
1
4
15

Its SQL Server. I tried entering the values like ('46', '45', '44') and it didnt work. – Anto Aug 04 '11 at 19:08
What's your data type? '45' assumes Type_ID is char/varchar/nvarchar data. If Type_ID is integer, you would use (46,45,44) instead. – VoteCoffee May 14 '15 at 14:19

Mohamad Mahmoud Darwish · Answer 3 · 2017-02-02T19:47:58.930

1

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+ ',%'

edited Feb 02 '17 at 19:47

answered Sep 28 '16 at 13:59

Mohamad Mahmoud Darwish

3,865
9
51
76

Pass string into SQL WHERE IN

3 Answers3

Linked