Showing posts with label separated. Show all posts
Showing posts with label separated. 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

How to use comma separated lists with parameters in sqldatasource?

I'm new (very new) to asp.net and now at 3am after maybe 12 straight hours of trying to go through examples and understand the syntax, I have a somewhat working program... basically a query parameters screen that upon a click generates a report. The query parameter screen basically is a HTML form with a procedure that puts the field values into session variables. The report uses <asp:sessionparameters> in the sqldatasource to apply aforementioned session variables to filter the SELECT statement's results. They are then displayed using the gridview control. It actually works!

Now I'm trying to figure out: if I need/want my users to be able to enter comma-separated list of values into the HTML form field and somehow get these into the SELECT statement's WHERE clause, is there a way to do this? I'm not very experienced with asp nor sql server and have been struggling with this new challenge. I really could use pointers as to whether there's an easy way to handle this. In other languages in the past I would have had to parse the comma-separated list and use those to construct my SELECT statement programmatically. I get the impression that with all this ASP.NET fanciness, there has to be a better way?

If it matters, I'm using ASP.NET 2.0 with SQL Server 2005 Express and Visual Studio Web 2005 Express.

Thanks in advance for any replies, they would be GREATLY appreciated :)

Hi,

you either generate the SQL query programmatically based on the CSV, e.g split the CSV string with the delimiter, loop the array returned by String.Split and then buold the SQl string dynamically. Other way is to create a stored procedure to the Db and use functions to split CSVs for example:http://www.sqlteam.com/item.asp?ItemID=11499