How to use comma separated value list in the where clause

[yasirzafar]

i want to do something like this;
for-example;
Set vote = true for all rows in tbl_emp where pk_tbl is in the comma separated value list.

update tbl_emp 
set vote=true where pk_tbl in @empl_vote_true

Where, @empl_vote_true = ’12,23,345,’ (IDs of the employees),

Is it possible, if so how?

[john garvey]

Hi Yarsirzafar

The IN Clause within WHERE should be enclosed in Parentheses i.e IN(6,45,233);

Try that
Kind regards

[Rabbit]

You can't do it that way. Not even if you put it in parentheses because the variable will always be a string. The IN clause will never see it as more than one value.

Unless you know the string will always contain the same number of items, you will have to build a dynamic string and then execute that.

[ck9663]

If you really need to, you can convert that string into a result set so you can use IN clause.

Here, read this...

Good Luck!!!


~~ CK

[john garvey]

As an update on my previous answer I will make the following assumptions;

Column 'vote' is type yes/no; true/false
column 'pk_tbl' is type integer
@empl_vote_true = "12,23,345"

sql= "UPDATE tbl_emp SET " _
& "vote = true " _
& "WHERE pk_tbl IN('" & @empl_vote_true & "');

Make sure you have the single and double quotes correct
This performs OK in Access 2007

Kind regards

[yasirzafar]

thanks for your answer, if that set is passed to a Stored procedure, via a variable whose datatype is string in c#, and varchar(8000) in sql server

string empl_vote_true = ",12,13,14,15,16"

Then at this stage,

sql= "UPDATE tbl_emp SET " _
& "vote = true " _
& "WHERE pk_tbl IN('" & @empl_vote_true & "')

will it work fine? or something should be added or parsed before. would i need a loop to update for each where clause, thanks

[john garvey]

I don't do c#, but it should work in MS SQLserver

[john garvey]

Hi Yasirzafar

When I looked at your code again the line

string empl_vote_true = ",12,13,14,15,1 6"

You should not have an opening comma without data it will throw an error.

Kind regards

[yasirzafar]

you mean, the string should be in the form of "12,13,14,15,16 ";
(for clarification). And it will work?

[john garvey]

The only way is to try it.

Kind regards