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

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