Skip to Content
author's profile photo Former Member
Former Member

Pass multiple values from 1 parameter to a stored procedure?

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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Jun 30, 2007 at 09:18 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Oct 15, 2007 at 06:43 PM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.