Passing variable from stored procedure to CR???

[KellyStee]

Ok, this is my issue:
We have a stored procedure with an amazing amount of records (1 million). The only data returned is constrained based on the 'Where' clause where a record had a field = ID (which is chosen by the user in the form of a stored procedure parameter).
In the report header, I want to show - NOT the specific ID chosen - but the description of the ID (which stems from a table made up of the ID and the description).
In a textbook scenario, I know I should set the parameter up to find records where the id = parameter value (this would be more efficient than having the parameter = the description, which is not indexed), but in order to display the ID DESCRIPTION, the only way I can think of accomplishing my task, is to return the description with EVERY record by adding it to the 'Select' clause, which just seems like a waste to me.
Is there a way to set up a variable in the stored procedure so that
v = id.description
where id.id = p_id
and then pass the variable (v) to CR so that instead of returning id.description 1,000,000 times, I only return it once???

Please help!!
Thanks!
Kelly
kelly.steensma@us.pwcglobal.com

 

[idle]

Why not pass the ID description as a field in the stored procedure by putting it in the SELECT statement of your SP, and then place this ID description field in the report header?
Each record then it will then contain the correct description for each ID field.

 

[balves]

I don't believe the Crystal "sees" parameters defined in a stored procedure as OUTPUT. It only sees the recordset created at the end of the stored procedure.

Why not a small sub-report that gets the description from the table based on the id?

 

[KellyStee]

Idle,
We don't want to add the Description field to the SELECT clause because you would be adding the Description 1,000,000 times to only show the value once (in the report header); all records returned should have the exact same ID and, therefore, the exact same description.

Balves,
I believe you are correct. We have experienced the same thing with OUT parameters - Crystal doesn't seem to "see" them.
A subreport would be an obvious answer, but we have heard bad things about subreports affecting the performance of reports. I don't know a lot about subreports; we try to avoid them at all costs. Do you think this is a valid concern?

Thanks for your help, guys!!
Kelly

 

[balves]

Nah, I've used sub-reports quite a bit.

In your case, you're fine as long as you don't put the subreport in the detail section. If you have 1 million rows in the detail section, putting a subreport in that section will mean it gets executed 1 million times as well.

A subreport in the report header should only get executed once.

 

[idle]

Kelly,
I misread that - Balves' suggestion is the simplest and is effective. If you wanted to do it within the SP, then you could add an extra field, but populate it with null values except for the first record. This might be a pain to do, depending on how your sp works. I did something very similar once (actually, it was about a dozen fields that I plugged in for one row only), but I was using a temp table in the sp, so it was quite simple to update the values for the first record with non null values.

 

[Guest_imported]

Hi,

I have the same sort of problem as KellyStee. Although I would like to pass out a number to use in a crosstab. My crosstab is basically setup to return budget items with their $ value per month. I want to also include a column that holds the $/total_units per month. I need to pass out the total_units. it is the same number for the entire cross tab. And, trying to avoid the same problem as KellyStee, I would like to avoid passing back the same number a million times and having to run a Sum($)/Max(total_units) or something lame like that. If anyone has any ideas it would be greatly appreciated.

Thanks

Shawn