Create an IN clause on the fly

Question

I have created a string of integers like this:

var whereClause = "4,5,6"

I need to use this in the following SQL where clause, where message.Id is an int col

select * from table a where message.Id in (@whereClause)

Running this throws Conversion failed when converting the nvarchar value '4,5,6' to data type int.

How would one cast the string, so that it could be used in a WHERE IN clause?

EDIT Appreciate this could possible be a repeat of a question, but I am using Dapper.NET and couldn't find any examples

No way to use a parameter to express a list of integers. It will be treated as a string — Steve, Feb 17 '14 at 13:53
Can you just use it as a collection? `var options = whereClause.Split(new[]{','}, StringSplitOptions.RemoveEmptyEntries);` — Jeroen Vannevel, Feb 17 '14 at 13:54
specifically see this answer: http://stackoverflow.com/a/337792/961113 — Habib, Feb 17 '14 at 13:55
Do you execute that sql in a stored procedure? You could create the sql string in C# (when you are certain you control where the integesers come from so you don;t expose yourself to SQL injection. — gjvdkamp, Feb 17 '14 at 13:55
i have provided an answer [here](http://stackoverflow.com/questions/21748327/how-to-write-stored-procedure-for-my-candidate-using-case-which-includes-skillid/21753829#21753829), check it out, it may be useful to you — Amir Sherafatian, Feb 17 '14 at 13:55
@Habib: it's worth noting that the third answer (acc. to the upvotes) using table-valued-parameters is the best approach if OP uses SQL-Server (>= 2008). — Tim Schmelter, Feb 17 '14 at 13:58
@TimSchmelter, yes, but may be a bit complicated to understand first but definitely a better option. — Habib, Feb 17 '14 at 14:00
Gents, I'm using Dapper.NET, so not sure how role in table-valued-parameters — CSharpNewBee, Feb 17 '14 at 14:06
@CSharpNewBee: http://stackoverflow.com/questions/6232978/does-dapper-support-sql-2008-table-valued-parameters — Tim Schmelter, Feb 17 '14 at 14:09

score 1 · Accepted Answer · answered Feb 17 '14 at 13:54

1

Quoting article Using comma separated value parameter strings in SQL IN clauses:

DECLARE @LIST VARCHAR(200)
SET @LIST = '1,3'
SELECT Id, Descr FROM CSVDemo WHERE Id IN (SELECT * FROM dbo.CSVToTable(@LIST))

answered Feb 17 '14 at 13:54

Piotr Stapp

19,392
11
68
116

Garath, can this be done without using the SQL function? From what you guys are posting, i believe not. – CSharpNewBee Feb 17 '14 at 14:01

score 1 · Answer 2 · answered Feb 17 '14 at 13:54

Unfortunately, you cannot do it like this: each item of the IN list needs to be bound individually.

First, you need to generate a SQL string with the proper number of parameters, like this:

select * from table a where message.Id in (@w1, @w2, @w3)

After that you need to bind each @wN individually.

score 1 · Answer 3 · answered Feb 17 '14 at 14:05

1

try:

exec ('select * from table a where message.Id in ('+@whereClause+')'

answered Feb 17 '14 at 14:05

LuisR9

116
3

score 1 · Answer 4 · answered Feb 17 '14 at 14:09

You can always use dynamic query but it could create some headaches in future, because the maintenance could be hard. The dynamic query should look like this:

SET @IDs = '4,5,6' 
SET @SQLQuery = 'SELECT * FROM table
WHERE ID IN(' + @IDs + ')'
EXECUTE sp_executesql @SQLQuery

Create an IN clause on the fly

4 Answers4