0

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?

insilenzio
  • 918
  • 1
  • 9
  • 23
  • Your comma separated value is being converted to a single integer – Hogan Jul 31 '14 at 10:38
  • possible duplicate of [Parse comma-separated string to make IN List of strings in the Where clause](http://stackoverflow.com/questions/17481479/parse-comma-separated-string-to-make-in-list-of-strings-in-the-where-clause) – insilenzio Jul 31 '14 at 11:44
  • I've already answered this in another question, please take a look at http://stackoverflow.com/questions/21160456/sql-server-procedure-declare-a-list/21160840#21160840 to see if this is acceptable to you – Peter Monks Jul 31 '14 at 11:59

2 Answers2

3

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))
juergen d
  • 201,996
  • 37
  • 293
  • 362
  • Thanks for replay! I've tried, but `exec('SELECT * FROM UserTable WHERE Id IN (' + @userId + ')')` return the same as `WHERE Id IN (@userId))`. – insilenzio Jul 31 '14 at 11:42
  • I updated the answer. You need to give your parameter a length: `(@userId VARCHAR(1000))` Otherwise it is just a single character – juergen d Jul 31 '14 at 11:50
0

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.

Community
  • 1
  • 1
insilenzio
  • 918
  • 1
  • 9
  • 23