CODEWITHSUNDEEP
t-sqlsql-server-2012
insilenzio
insilenzio

Reputation: 938

Using a comma separated varchar for a IN clause

I've a list of userId as DECLARE @userId AS VARCHAR = '1,4,65,12' that I'd like to use in a IN clause. The result should be like WHERE Id IN (1,4,65,12).

I've tried the following code: 

ALTER FUNCTION [dbo].[GetUser](@userId VARCHAR)
RETURNS TABLE AS RETURN(
    SELECT *
    FROM UserTable
    WHERE Id IN (@userId))

but it works only for first int value. In this example it works only for 1.

Any idea?

Upvotes: 0

Views: 2634

Answers (2)

juergen d
juergen d

Reputation: 204746

You can't use the IN clause like that. It compiles to a single string in your IN clause. But an IN clause needs seperate values.

WHERE id in (@userId)

compiles to

WHERE id in ('1,4,65,12')

but it should be

WHERE id in (1,4,65,12)

If you really need the query to be dynamic then you can use 

exec('SELECT * FROM UserTable WHERE Id IN (' + @userId + ')')

And you need to give your input parameter a length like

ALTER FUNCTION [dbo].[GetUser](@userId VARCHAR(1000))

Upvotes: 4

insilenzio
insilenzio

Reputation: 938

I've found this question wich is the same: Parse comma-separated string to make IN List of strings in the Where clause.

I'm going to try this solution.

Upvotes: 0

Related Questions