using comma separated list in where clause

Uma Kameswari

Hi, I am having a problem with the below statements when I give as below I am not getting any records.

declare @list char(100)
set @list = '''wc1'',''wc2'''
select * from iv30300 where trxloctn in (@list)

When I directly give the list I am getting the result.

declare @list char(100)
set @list = '''wc1'',''wc2'''
select * from iv30300 where trxloctn in ('wc1','wc2')

I am working on sql server 2005 version. I am not able to find out the error.

Wendelius

AFAIK you cannot use variable to define an in-list with comma separated values. The contents of the variable is interpreted as a single value, not a value list. Also you wouldn't need the quotation marks inside the string. For example the following would work:

declare @value1 varchar(100)
declare @value2 varchar(100)
set @value1 = 'wc1'
set @value2 = 'wc2'
select * from iv30300 where trxloctn in (@value1, @value2)

Perhaps you could modify the in-list to a subquery.

The need to optimize rises from a bad design.My articles[^]

Paddy Boyd

There's an not error, you just can't do it the first way, the in clause doesn't take a variable like that.

pmpdesign

Use XML

DECLARE @CSV XML
SET @CSV = '<key><id>1</id><id>2</id><id>3</id></key>'

SELECT
ParamValues.id.value('.','VARCHAR(100)')
FROM
@CSV.nodes('/key/id') AS ParamValues(id)

Run that and you should get three rows with values 1,2 & 3. Join that to your query or use in the WHERE clause Don't forget to parse the XML before presenting it to SQL in case there are any nasties on the input

Ben Fair

Put the values into a table variable and join to it; in our projects we have a udf that parses a comma-separated string and returns a table variable of the data, it's not too hard to make one. Do that and then join to the table variable.

Keep It Simple Stupid! (KISS)