You could try something like this.

DECLARE @smsCriteria varchar(15)
DECLARE @smsCriteria2 varchar(15)
SET @smsCriteria = 'test1'
SET @smsCriteria2 = 'test2'
select Crit.Criteria, Crit.CID from Crit 
where Crit.Criteria =(@smsCriteria)or where Crit.Criteria =(@smsCriteria2)
GO


Dave

-----Original Message-----
From: carl@o... [mailto:carl@o...]
Sent: Friday, February 22, 2002 8:34 AM
To: sql language
Subject: [sql_language] Argh! it seems so simple! problems using 'IN'...


The really infuritating thing is that if I cut and paste the select 
statement below into query analyzer and run it, it works! (obviously I 
replace the variable with an actual value!)

What works in query analyzer is:
select Crit.Criteria, Crit.CID from Crit where Crit.Criteria IN 
('test1', 'test2')

This returns all 19 rows that it should

However, if I have the same line in my stored procedure and pass 
@smsCriteria through, I get no rows returned!:

select Crit.Criteria, Crit.CID from Crit where Crit.Criteria IN 
(@smsCriteria) 


I thought I may be passing the string through wrong (I'm doing 
@smsCriteria='''test1'', ''test2''') so I used a 'print line to print out 
the value.  It returns 'test1', 'test2' so it is exactly right.  Why then 
does it not return the 19 rows I want?

Help! Please!

$subst('Email.Unsub').