Passing comma separated list to a stored procedure

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 + ' ' )
LVL 35
YZlatAsked:
Who is Participating?
 
Thandava VallepalliConnect With a Mentor Commented:
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
0
 
Thandava VallepalliCommented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.