Passing comma separated list to a stored procedure

YZlat
YZlat used Ask the Experts™
on
I have a stored procedure that takes a list of values as an input argument. The list is in the format:

'DFG','ABC','ASD','FGH'

I want the result to be

SELECT field1, field2, field3 FROM Table1
WHERE UPPER(RIGHT(RTRIM(field1),3)) IN ('DFG','ABC','ASD','FGH')

here is my stored procedure. What am I doing wrong here?

CREATE PROCEDURE dbo.sp1
@list as varchar(200)
 AS

SELECT field1, field2, field3 FROM Table1
WHERE UPPER(RIGHT(RTRIM(field1),3)) IN ( ' ' + @list + ' ' )
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
try this one

CREATE PROCEDURE dbo.sp1
@list as varchar(200)
 AS

exec ( 'SELECT field1, field2, field3 FROM Table1 WHERE UPPER(RIGHT(RTRIM(field1),3)) IN ( ' + @list + ' ) '


itsvtk
Ooops...!  forgot the ')' at end....

try this one

=======================
CREATE PROCEDURE dbo.sp1
@list as varchar(200)
 AS

exec ( 'SELECT field1, field2, field3 FROM Table1 WHERE UPPER(RIGHT(RTRIM(field1),3)) IN ( ' + @list + ' ) ' )
=========================



and you need to call this proc as below

exec sp1 @list = '''DFG'',''ABC'',''ASD'',''FGH'''



itsvtk

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial