For performance reasons, I've had to take a very complex command that I was using in a report and re-work it in a stored procedure. Actually, it's complex enough with lots of dynamic SQL generation based on the parameters so I've had to put it into a package. (The result set always returns the same fields, it just the joins and where clauses in multiple sub-selects that change.)
The procedure works great - I' seeing a 10 to 20-fold performance increase and in testing I'm showing that it's returning the correct data.
However, when try to use the procedure in a report, it's not returning any data even though the sample report is using one of the same sets of parameters that I've been testing where I know the SP returns data.
Here's how I've accessed the SP:
1. In the Database Expert, open the connection, go to the correct Schema, and the to Qualifiers. Here I see all of the packages in this schema.
2. Open the package that I need and select my SP.
3. Enter values for all of the parameters (2 datetimes and 5 strings).
I put the fields from the SP on the report, but no data appears.
HELP!! The project manager has committed to a couple of the major users that we will have something available for them to test tomorrow.
Thanks!
-Dell
IP Logged