Print Page | Close Window

DEFINE ARRAY DATA TYPE IN STORED PROC

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2022
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=884
Printed Date: 05 May 2025 at 3:43am


Topic: DEFINE ARRAY DATA TYPE IN STORED PROC
Posted By: peter
Subject: DEFINE ARRAY DATA TYPE IN STORED PROC
Date Posted: 19 Jun 2007 at 4:45pm
Hi,
I'm trying to define a passing parm as ARRAY but could not get any luck. Please Help!

Here is what I have: a DB2 Stored Procedure

CREATE PROCEDURE SEL_P100N (
    IN p_user VARCHAR(3)
)
LANGUAGE SQL
RESULT SETS 1
P1: BEGIN
...
...
END P1;

And I'm trying to do:

CREATE PROCEDURE SEL_P100N (
    IN p_users[100] VARCHAR(3) -- ???? I WANT p_users an ARRAY but  could Not find a way ?????
)
LANGUAGE SQL
RESULT SETS 1
P1: BEGIN
...
...
END P1;


Replies:
Posted By: BrianBischof
Date Posted: 19 Jun 2007 at 8:28pm
Can't do it. You can only use arrays within a formula and even then you can't return an array from a formula. Nor can you pass an array as a parameter to a SP. What about returning a comma delimited string and then in CR call the Split() function to convert it to an array in the report?

-------------
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>

Posted By: peter
Date Posted: 20 Jun 2007 at 9:23am
Hi Brian,
Thank you for your reply. However, our need is to have the array in the SP so that we can filter/reduce the data come over onto CR.
I was thinking about sending the string with Dilimeter such as AAA,BBB,CCC but this is not a desirable approach because we want to validate the items in the string against a DB2 table before sending over to the SP. And you know CR is not doing that well.
 
By the way:
Another question: Instead of using SP, we plan to connect and do table joins all on CR. But from looking at the Show Query in CR, it does not show the WHERE clause used with Parameters. So the question is: Does CR bring ALL data over then filter then based on the inputs in the Parameters ? We dont want that. We want the data come in to CR as much as the inputs from the Parameters !!!!

Posted By: BrianBischof
Date Posted: 20 Jun 2007 at 9:51am
The WHERE clause is tricky because CR "wants" to have the database do as much work as possilble. But the circumstances has to be right. First of all, the record selection formula has to be simple. If you have any formulas in it then it won't get converted to SQL. Plus, sometimes simple formulas won't get passed either and you have to play around with it to get it just right. Also, you have to set the SQL optimization settings in the report options. Look for the settings relating to grouping on server and optimizing server speed (I forget the actual option name right now).

Doing these things should get your WHERE clauses off the client and onto the server.

-------------
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>

Posted By: peter
Date Posted: 20 Jun 2007 at 10:10pm

Again, thank you, Brian.

I really want to use SP with a way of passing parm as array so that I can use the Allow Multi Values option in the Parameters. Otherwise, that option is disabled.


Print Page | Close Window