Pass multiple values from 1 parameter to a stored procedure?

[Former Member]

Post Author: Rdev

CA Forum: Data Connectivity and SQL

I have a Crystal Reports 11 report that uses a SQL stored procedure to access records from some views. I need to pass 1 or many ContactID values to the stored procedure so that the record filtering happens on the stored procedure side, not in Crystal Reports. ContactID is an integer data type.

What do I need to do on the Crystal reports side to pass multiple ContactID's to the stored procedure and how should my stored procedure be created to accept multiple ContactID values from Crystal Reports?

I have searched all over for an answer but have not found one that works for me.

Any help will be appreciated and if you could point me to some examples, that would also help me.

Thanks in advance,

Rdev

[Former Member]

Post Author: Slavik

CA Forum: Data Connectivity and SQL

May be SQL Expression Field could be usefull in this case. Here is the quote from CR Help:

To maintain optimum report processing speeds, avoid using formulas(whether Crystal or Basic syntax) within record selection formulas.Instead, replace the original formula with an equivalent SQL Expression field,and then incorporate the SQL Expression field into your record selection formula.Doing so will greatly improve the chances of your record selection being pushed down to the server.

[Former Member]

Post Author: synapsevampire

CA Forum: Data Connectivity and SQL

Most types of database's stored procedures can't take arrays as parameters (please take the time to post your database type), so it doesn't matter what tool you are using as a front end.

Many people address this by passing character seperated values (CSV) to the SP, but this places the requirement on the user to construct a proper CSV, which is generally a bad idea.

The alternative is to create a View which joins all of the Views and pass the multiple value parameter to that View. It may be slower than an SP, then again it might not be significantly different.

-k