Passing parameter to SQL select statement IN clause acts weird.

Question

I've got the following query that returns 2 records (in DataSet's query builder)

SELECT        EmpID, Name, id
FROM          Users
WHERE        (CAST(id AS Varchar(20)) IN ('5688','5689'))

Now if I do the same query passing the parameter instead from code behind: String param = "'5688','5689'"; it returns null.

WHERE        (CAST(id AS Varchar(20)) IN (@param))

I tried taking off the very first and last ', but that did not make a diffrence.

!!!id is a unique PK!!!

Anyone's got a clue?

Could you please post the code that contains the specified parameters? — Lion, Apr 29 '12 at 17:10

Jake · Accepted Answer · 2012-04-30T18:57:11.187

3

The solution I found is quite simple, this works like a charm and there's no need for sps or other functions;

SQL:

SELECT whatever 
FROM whatever
WHERE (PATINDEX('%''' + CAST(id AS Varchar(20)) + '''%', @param) > 0)

C#:

String param = "'''1234'',''4567'''";
dataTable1 = tableAdapter1.getYourValues(param);

edited Apr 30 '12 at 18:57

answered Apr 30 '12 at 18:49

Jake

169
2
16

1

Here's a pretty comprehensive discussion on ways to do this. http://stackoverflow.com/questions/337704/parameterizing-an-sql-in-clause/337792 – Mark Brackett Apr 30 '12 at 19:02
This is just what I needed. My dataset designer kept defaulting my parameter to be `DbType = Object`, which the SQL didn't like so I had to update the parameter to be `DbType = String`, but then it worked like a charm. – Cory Jul 07 '17 at 18:15

score 1 · Answer 2 · answered Apr 29 '12 at 17:13

A variable is not allowed in the IN clause.
You are expecting the values as a comma delimited string you could use the split function (user defined and non-standard) to join them with the original tables:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=326300&SiteID=1

For more information you can visit this

score 0 · Answer 3 · answered Apr 29 '12 at 17:11

0

('5688','5689') is an array of values.

Defining String param = "'5688','5689'"; and using it as (@param) makes ('5688','5689') a string. Which wont work.

answered Apr 29 '12 at 17:11

Bibhas Debnath

14,559
17
68
96

score 0 · Answer 4 · answered Mar 19 '13 at 15:40

0

Bibhas is correct. For me this worked:

string param="'1234','4567'"; we can't use param as SQL Parameter(@param).

command = new SqlCommand("SELECT * FROM table WHERE number IN (" + param + ")", connection);

command.ExcecuteReader();

answered Mar 19 '13 at 15:40

Praveen

55,303
33
133
164

You append your values directly into SQL but we try to pass values by parameter. In my opinion your SQL can injectable. – Yasin Yörük Jun 18 '15 at 14:26

Passing parameter to SQL select statement IN clause acts weird.

4 Answers4

Linked