Question
How to pass multiple strings through a parameter in custom SQL. Is there any way to pass comma separated values into a parameter and use it in custom SQL?
How to pass multiple strings through a parameter in custom SQL. Is there any way to pass comma separated values into a parameter and use it in custom SQL?
The query below applies to SQL for Oracle.
select * from tablename where name in (
select regexp_substr(<Parameters.Parameter1>,'[^,]+', 1, level) from dual
connect by regexp_substr(<Parameters.Parameter1>, '[^,]+', 1, level) is not null)
The query below applies to SQL for Microsoft SQL Server.
SELECT *
FROM Test.dbo.tablename a
JOIN (
(SELECT Number = ROW_NUMBER() OVER (ORDER BY Number),
Item FROM (SELECT Number, Item = LTRIM(RTRIM(SUBSTRING(<Parameters.Parameter1>, Number,
CHARINDEX(',', <Parameters.Parameter1> + ',', Number) - Number)))
FROM (SELECT ROW_NUMBER() OVER (ORDER BY s1.[object_id])
FROM sys.all_objects AS s1 CROSS APPLY sys.all_objects) AS n(Number)
WHERE Number <= CONVERT(INT, LEN(<Parameters.Parameter1>))
AND SUBSTRING(',' + <Parameters.Parameter1>, Number, 1) = ','
) AS y)) x on a.colname = x.Item