MultiSelect Parameter with Stored Procedure not working

[Former Member]

I am using Crystal Reports 2008 and SQL Server 2008. I am using a stored procedure which works with a multiselect in SQL side. I am using a string to table utility function in the where clause with the In statment like below:

WHERE FORMULARY_ID in

--(@FORMULARY_ID)

(SELECT Extractedtext

FROM ClarityTST.[rpt].[LHfn_StringToTable]( @FORMULARY_ID, ',') )

When I try to use the stored procedure I get an error message:

"The stored procedure cannot accept multiple values. Please ensure that the multiple value is set to False:"

I would like to use it with multiple parameters.

[Former Member · Accepted solution]

Crystal uses arrays to handle multiple values. As far as I'm aware, there is no way to get a multi-valued report parameter into SQL (command or SP) directly. In the past, I have used a "shell" main report that gets the parameters, used Join() to concatenate the selected values into a single string, then pass that to the "main" subreport, and into its SQL or SP. The SQL than would need to find the database field in the string. If you make the string passed to the subreport valid SQL for an IN clause, you could do it that way, too.

HTH,

Carl