Passing values to SQL Server stored procedure 2

[Eddie1979]

We are currently working on some Crystals which use stored procedures in SQL Server. When we create a parameter in a normal report (based on tables and queries), we can assign it to be either discrete, range or discrete and range, all with multiple values.

When we add our stored procedure to a new Crystal the parameters are automatically setup based on the procedure. Unfortunately (for us!) they do not allow either multiple values or range+discrete values.

Does anyone know how we can get around this? I think it may involve changing the stored procedure (which would be fine). If I find the solution I will post it here.

Many thanks,
Eddie.

 

[vidru]

Until SQL Server allows arrays in stored procedures (Yukon, I've heard), there's no clean way to do this, and even then, who knows if it'll work. Possible workarounds:

1) Avoid using a procedure for this and create a view.
2) Rewrite the stored proc to accept a comma separated list of values, then parse them in the procedure. Downside is that the users will have to type in the comma separated list, as opposed to using the multiple-pararmeter 'Add' thingy in the CR default parameter window.
3) Rewrite the procedure (as in option 2). Create a blank report (no tables), create your parameters, add the report based on the stored proc as a subreport, then create formulas on the main report to create comma separated lists for the parameters, and link to the subreport using your formulas. This is a lot of work, so I don't know it it'd be worth it.

-dave