Crystal Reports XI and MySQL Stored Procedure with Parameters

Question

I am having a problem with a Crystal Report that displays data from a MySQL table. I am currently gathering the data directly from the table, however when the users try to input parameters, problems arise such as:

1. null values for parameters returning errors
2. parameters not working as specified

I then created a stored procedure to return data if a parameter is empty and will make the MySQL server do the work rather than the Crystal Reports server.

However Crystal Reports doesn't appear to recognize this and I am having some trouble displaying the results of the procedure.

Here is a copy of the procedure i am using:

Create Procedure sp_report
(IN @param1 varchar(64),
 IN @param2 varchar(64),
 IN @param3 int )

Begin

IF @param1 is null AND @param2 is null AND @param3 is null Then
  Select * from tblData
ELSE IF @param1 is null AND @param2 is not null AND @param3 is not null then 
  Select * from tblData where field3 = @param3 and field2 = @param2
ELSE IF @param1 is not null AND @param2 is not null AND @param3 is  null then 
  Select * from tblData where field2 = @param2 and field1 = @param1
ELSE IF @param1 is not null AND @param2 is null AND @param3 is not null then 
  Select * from tblData where field3 = @param3  and field1 = @param1 
ELSE IF @param1 is not null AND @param2 is null AND @param3 is null then 
  Select * from tblData where field1 = @param1
ELSE IF @param1 is  null AND @param2 is not null AND @param3 is  null then 
  Select * from tblData where  field2 = @param2
ELSE IF @param1 is null AND @param2 is null AND @param3 is not null then
  Select * from tblData where field3 = @param3
ELSE IF @param1 is not null AND @param2 is not null AND @param3 is not null then 
  Select * from tblData where field3 = @param3 and field2 = @param2 and field1 = @param1
END;

Is there an easier way to do this or am I doing something wrong? Any suggestions would be greatly appreciated.

Answer 1

If I'm reading your IF tree correctly, I think you could do this instead (I'm a T-SQL guy, so I can't confirm if this will run in MySQL):

SELECT *
  FROM tblData
 WHERE ((field1=@param1) OR (@param1 is null))
   AND ((field2=@param2) OR (@param2 is null))
   AND ((field3=@param3) OR (@param3 is null))

Answer 2

I can't help with the mySQL part, but I do something very similar with both SQL Server and Oracle. Instead of a series of IF statements covering every possible combination of null/not null parameters, I do something like this:

select * from tblData where (field1 = @param1 or @param1 is null) and
(field2 = @param2 or @param2 is null) and
(field3 = @param3 or @param3 is null)

That covers any mix of null and non-null parameters without exponentially increasing the number of IF statements as the number of parameters increases.

This is a complete guess on my part, but in Oracle you need to declare a cursor as an IN OUT parameter for SPs to work with Crystal. Could mySQL have the same requirement? Here's a sample of an Oracle SP:

create or replace PROCEDURE      SomeProcedure
(
    param1               IN       VARCHAR2 DEFAULT null,
    param2               IN       VARCHAR2 DEFAULT null,
    REPORT_CURSOR        IN OUT     CrystalPkg.CrystalCursor
)
AS
BEGIN

    OPEN REPORT_CURSOR FOR
    SELECT blahblah from blah...

Answer 3

select * 
from tblData 
where field1 like isnull(@param1,'%%' 
and field2 like isnull(@param2,'%%')
and field3 like isnull(@param3,'%%')