Geek Answers Handbook

Passing a String in SQL WHERE IN

I am working on a query page where the user selects a value representing different types, each of which is identified by an identifier. The problem is selecting these identifiers from the database 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 a single value, for example. 46, but NOT if the value is in parentheses, for example. (46) or ('46'), as it should be for IN.

I use visual studio, which automatically generates a method to access the table adapter to get the values, so I think I should do this through SQL.

I pass a string, for example. Types = "46.267,2010", into an adapter method that passes a string to @Types in an SQL statement.

Any help would be great. Thank!

+5
3

- , TSQL . , , , - , IN() .

:

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

...

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

, , .

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

SQL Server Oracle, MySQL?

, ,

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

, , , ( - , )

+5

Select :

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

More articles:


All Articles