By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,575 Members | 1,413 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,575 IT Pros & Developers. It's quick & easy.

How to use comma separated value list in the where clause

P: 6
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.

Expand|Select|Wrap|Line Numbers
  1. update tbl_emp 
  2. set vote=true where pk_tbl in @empl_vote_true
  3.  
Where, @empl_vote_true = 12,23,345, (IDs of the employees),

Is it possible, if so how?
Jul 26 '12 #1
Share this Question
Share on Google+
9 Replies


P: 50
Hi Yarsirzafar

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

Try that
Kind regards
Jul 26 '12 #2

Rabbit
Expert Mod 10K+
P: 12,430
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.
Jul 26 '12 #3

ck9663
Expert 2.5K+
P: 2,878
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
Jul 26 '12 #4

P: 50
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
Jul 26 '12 #5

P: 6
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

Expand|Select|Wrap|Line Numbers
  1. string empl_vote_true = ",12,13,14,15,16"
Then at this stage,

Expand|Select|Wrap|Line Numbers
  1. sql= "UPDATE tbl_emp SET " _
  2. & "vote = true " _
  3. & "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
Jul 26 '12 #6

P: 50
I don't do c#, but it should work in MS SQLserver
Jul 26 '12 #7

P: 50
Hi Yasirzafar

When I looked at your code again the line

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

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

Kind regards
Jul 27 '12 #8

P: 6
you mean, the string should be in the form of "12,13,14,15,16";
(for clarification). And it will work?
Jul 28 '12 #9

P: 50
The only way is to try it.

Kind regards
Jul 29 '12 #10

Post your reply

Sign in to post your reply or Sign up for a free account.