'WHERE IN' in T-SQL

Question

Possible Duplicates:
Parameterizing a SQL IN clause?
Is it possible to use query parameters to fill the IN keyword

Hi, I'm using WHERE IN in my SQL query as the user may need to enter multiple id's to show records for , seperated by commas. I have this at the moment:

WHERE Consignments.ID IN(@ID)

Where @ID will be the list of ID's seperated by commas. Problem is it works when I the parameter has one number - if there's more than one then it returns no rows. Any ideas?

Look carefully at the value of @ID. Is it properly comma-delimited? Are the numbers passed as numbers or as strings surrounded by single quotes? — DOK, Jan 19 '11 at 20:43
possible duplicate of [Is it possible to use query parameters to fill the IN keyword](http://stackoverflow.com/questions/589931/is-it-possible-to-use-query-parameters-to-fill-the-in-keyword) and [HOW to convert CSV to record set inside T-SQL?](http://stackoverflow.com/questions/1432002/how-to-convert-csv-to-record-set-inside-t-sql) — LukeH, Jan 19 '11 at 20:44

score 2 · Answer 1 · answered Jan 19 '11 at 20:43

2

In cannot be used in this way. You will need to do this by creating the SQL dynamically.

DECLARE @sql VARCHAR(200);
DECLARE @id VARCHAR(200);

SET @sql = 'SELECT * FROM Table WHERE Consignments.ID IN (' + @id + ')';

EXEC sp_executesql @sql;

answered Jan 19 '11 at 20:43

Neil Knight

47,437
25
129
188

1

+1 This is the SQL variant, you could also generate the SQL in C# and pass it as CommandText – Andomar Jan 19 '11 at 20:47
2

It's not a safe solution, in addition it has a low performance – Jahan Zinedine Jan 19 '11 at 20:48
@Jani: Certainly agree about the safety, but I'm merely showing how to do it :o) – Neil Knight Jan 19 '11 at 20:51

score 1 · Accepted Answer · answered Jan 19 '11 at 20:42

1

You need to use dynamic sql or use a stored proc with a table varaiable as in input varaible.

answered Jan 19 '11 at 20:42

HLGEM

94,695
15
113
186

+1, TVP's in SQL 2008 are the correct way to handle this – Matthew Jan 19 '11 at 20:47

score 0 · Answer 3 · answered Jan 19 '11 at 20:41

0

You will need to have the parameters comma separated not just passing a string of comma separated ID's.

WHERE Consignments.ID IN(@ID1,@ID2,@ID3,...)

answered Jan 19 '11 at 20:41

Joe

56,979
9
128
135

But, then you'll need to know up front how many values your IN clause should contain. – Frederik Gheysels Jan 19 '11 at 20:43
Take a look @ Ardman's example to do it in SQL. – Joe Jan 19 '11 at 20:45

score 0 · Answer 4 · edited May 23 '17 at 10:33

0

You cannot use a parameter in a SQL statement, when this parameter has to be used in an IN clause.

The reason is simple, you have to define that parameter as a string when it contains multiple values since 1, 5, 6 is not a valid number (assuming that ID is a numeric field).

Therefore, you'll have to use string concatenation in order to make this work.

This was a similar topic, a few days ago.

edited May 23 '17 at 10:33

Community

1
1

answered Jan 19 '11 at 20:41

Frederik Gheysels

56,135
11
101
154

score 0 · Answer 5 · answered Jan 19 '11 at 20:44

0

It should be either a comma separated list of values or IDs selected in a query:

WHERE Consignments.ID IN('1','2')
OR
WHERE Consignments.ID IN(SELECT ID FROM <Table | View>)

answered Jan 19 '11 at 20:44

kateroh

4,382
6
43
62

score 0 · Answer 6 · answered Jan 19 '11 at 20:46

You cannot pass literal comma-separated values as a parameter to be used with IN because IN will resolve the variable as a set (of one item, a string) rather than creating a set from your literal.

If you want to do it this way, you need to store your entire statements as a string, then use EXEC to run it... this will make your query non-sargable though and vulnerable to injection.

If you have SQL 2008, you should be using the TVP instead. You will actually pass in a table set of your values.

'WHERE IN' in T-SQL

6 Answers6

Linked

Related