Link to home
Start Free TrialLog in
Avatar of YZlat
YZlatFlag for United States of America

asked on

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 + ' ' )
Avatar of Thandava Vallepalli
Thandava Vallepalli
Flag of United States of America image

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
ASKER CERTIFIED SOLUTION
Avatar of Thandava Vallepalli
Thandava Vallepalli
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial