How to use comma separated value list in the where clause?

How to use comma separated value list in the where clause?

I would like to do something like the following (Set voted = true for all rows in tblVoters where EmpID is in the comma separated value list).

update tbl_Voters

set voted = true

whereEmpID in @.empIdsCsv

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

Since the above is not possible I have done the following dynamic query:

-- Convert the comma separated values to conditional statement like EmpID = {id} or EmpliD = {id}…

set @.empIdsCsv = 'EmpID=' + substring(@.empIdsCsv , 0, len(@.empIdsCsv ))-- Remove trailing comma

set @.empIdsCsv = replace(@.empIdsCsv , ',', ' or EmpID=')

declare @.markVoters varchar(8000)

set @.markVoters = '

update tbl_Voters

setvoted = true

where ’ + @.empIdsCsv

--Execute the dinamic query

exec (@.markVoters)

The above code generates the following dynamic query:

update tbl_Voters

setvoted = true

where

EmpID= 12 or EmpID=23 or EmpID=345

The obvious drawback here is the performance and the limitation of the dynamic query length (8000 chars).

Can someone suggest a better solution with the ability to use comma seperated values in the where clause?

there is the IN clause however I somehow get the feeling that will not help performance that much. The syntax is
select columns from tablename where EMplID IN (12, 23, 356, ....)

Hope this helps
|||

Please take a look at the link below that describes various techniques on how to pass a list of values from client to SQL Server.

http://www.sommarskog.se/arrays-in-sql.html