SQL help: IN clause and single quotes

[rh71]

UPDATE>> resolved (was going to remove this but maybe it will help someone)

FIX>> use preservesinglequotes function (first time I've seen a case for it)
Thanks KLin, I didn't see there were already responses. You're spot on.

ISSUE>> SQL Server 2008 - without getting into too much background, I am trying to run a query through ColdFusion and having an issue:

I set a variable like:
myList = 's1','s2','s3'
because I am dropping that in an IN clause and when displaying #myList# it accepts and shows exactly the above: 's1','s2','s3'

But when I run that in CF as part of a SQL IN clause:
select trialName from table where trialName NOT IN (#myList#)

It auto-inserts ADDITIONAL apostrophes for some reason so it looks like:
select trialName from table where trialName NOT IN (''s1'',''s2'',''s3'')
and so the query errors out with incorrect syntax near 's1'.

All I'm trying to do is get it to run exactly as I set the variable the first time, with single quotes. Why is it interpreting it with additional quotes?

I can't do a subselect instead because the table is actually a function and it timesout when using a subselect of itself too. Maybe I can make use of an array somehow?

 

[KLin]

http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-6cbf.html

Give that a try?

 

[cbrunny]

This might seem like a silly question, but you say it auto-inserts additional apostrophes. Is it as you've written it or is it like this: ''s1','s2','s3''?

If it as I've written it, I'd guess that you've set myList to equal one static variable that is literally this: 's1','s2','s3'

instead of setting three possible values.

But I don't know anything about ColdFusion, so apologies if that's stupid suggestion.