Showing posts with label voted. Show all posts
Showing posts with label voted. Show all posts

Wednesday, March 7, 2012

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