How to pass mutiple-value parameters to a SQL server stored procedure? 1

[lyu]

Hi,

I use CRXI and Sql Server 2000. I want a report, that users can choose multiple student IDs they want to see, then the report passes these mutliple IDs to a stored procedure as parameters, the sp runs and returns records which are only related to the students the user has chosen.

The difficutly, as I understand, is that a user might choose 1, 2, or any number of student IDs, and a stored procedure cannot take an array as its parameter. Obviosuly, I cannot write endless stored procedure like myProc(studentID1), myProc(studentID1, studentID2)....

I know one solution to that, is to let the sp return all records and let crystal report do the filtering thru "selection expert", however, this is not an ideal solution. We would rather let the database do the filtering, since the data is huge and the sp is very complicated, e.g. it may take 10+ minutes to return all the records, while frequently, a user may only want to see 5-6 records of them.

Can anyone here give me some ideas of how to implemnt it?
I desperately need to get it done. Many Thanks!!

 

[MisterMo]

set your Id param as a string in your SP so that you can pass multiple values from Crystal Report

eg. @MyId = '23,444,543,777,999'

create MySp
@MyId varchar(200)
as
begin
set nocount on

declare @MySql Varchar(3000)

set @MySql = 'Select MyFiedId, myFieldName, MyFieldTelephone from MyTable
where MyFiedId in (' + @MyId + ')'

exec(@MySql)
end

Make sure that the size of @MySql is big enough to hold the whole query.


This usually does the trick for me

Mo

 

[lyu]

Thank you very much, MisterMo

If you set the parameter as a single string, Then how do you design the parameter interface so it will take multiple values? I tried both CR10 and CRXI, they both won't allow me to set multiple values for the parameter @MyId, the option is simply disabled.

Any ideas? Thanks again!

 

[vidru]

The workaround would be to create a blank report with a parameter to get your ID's. Then, import your current report as a subreport, and link the parameters using a formula that puts the chosen parameters into a single comma separated string.

If the datatype of your StudentID field is a string, then it would be as easy as creating a formula using the Join function to create the list:

Join({?YourParameter}, ",")

...but I have a feeling you're dealing with numbers. In that case, you'd have to use a looping formula to create the list:
[tt]
//{@StudentIDs}
NumberVar Array Students := {?YourMultiValueParameter};
NumberVar i;
StringVar output;

for i := 1 to UBound(Students) do(
output := output + ToText(Students, 0, "") + ",";
);

output := Left(output, Len(output) - 1);
[/tt]
In your subreport linking, you'd link the stored procedure parameter to the {@StudentIDs} formula.

-dave

 

[lyu]

Yeap, to use a subreport is a really a good idea, except that subreport doesn't support page. In my report, I need to at least have a page counter in each page since the report will be long. I know that we can fake a page header and footer in subreport using grouping, however, it sometimes makes the report a bit messy, especially page footer. Is there any other solutions?

Guys, thank again for all your help!

 

[synapsevampire]

I agree that parsing the parameter in the SP is the simplest fashion, however as you've learned this does not allow for multiple selections, the user would have to create a comma delimited string or some such and then parse it in the SP for the data you require.

You can also write a front end to handle this, but also consider using a View instead of an SP if a View can handle your requirements. You can pass multiple value parameters to Views.

Sadly this is a limitation of SPs, in that you can only pass discrete values.

It's rumored that the next version of SQL Server will allow for this, we'll see...

-k

 

[bobschr]

I've tried to implement the main report / subreport approach in Crystal XI. The subreport renders fine UNTIL I link a Formula Field to it. No matter what formula field I link to the subreport the subreport will no longer render (and the sub reports stored procedure does not fire)...any thoughts on what might cause this? Thanks...

 

[lyu]

I just tried the main/sub report approach in both CR XI and 10, it doesn't work for me either, the same problem as bobschr, any ideas?

Thanks a lot!!!