Passing parameter with list of values to sql command using IN function

[crystaldev1]

I have a parameter setup in the sql command of the report as follows:

select *
from table
where name in (?ParameterName)

This parameter has more than one values. So I have values already setup in the parameter (e.g. John Smith, Bob Hope, Steve Johnson, etc.). I can add more names as I wish. So I would like use the parameter so that sql command will treat it as the following:

..
where name in ('John Smith','Bob Hope','Steve Johnson').

Thanks.

 

[crystaldev1]

any thoughts?

 

[briangriffin]

What version are you using? I think it was 2008 that introduced multivalue parameters within command objects.

 

[PeterDimitrov]

You can pass the values separated by commas, for example 'John Smith,Bob Hope,Steve Johnson'. Parse them to a temp table and include it in your query.


select *
from table t inner join fnTextToTable(?ParameterName) v ON t.name = v.ID

Check this article to see different ways to parse the values and sample fnTextToTable function:

http://www.r-tag.com/Pages/MultivalueParametersWithSQLSP.aspx

http://www.R-Tag.com

Viewer and Scheduler for Crystal reports and SSRS.