带有参数的Crystal Reports XI和MySQL存储过程

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:

我遇到一个Crystal Report的问题,它显示MySQL表中的数据。我目前正在从表中直接收集数据,但是当用户尝试输入参数时,会出现以下问题:

null values for parameters returning errors

返回错误的参数的空值

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.

然后,如果参数为空,我创建了一个存储过程来返回数据,这将使MySQL服务器完成工作而不是Crystal Reports服务器。

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

但是Crystal Reports似乎没有认识到这一点,我在显示程序结果时遇到了一些麻烦。

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.

有没有更简单的方法来做到这一点,或者我做错了什么?任何建议将不胜感激。

3 个解决方案

#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):

如果我正确地读取你的IF树,我认为你可以这样做(我是一个T-SQL人,所以我无法确认这是否会在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))

#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:

我无法帮助mySQL部分,但我做了与SQL Server和Oracle非常相似的事情。而不是涵盖null / not null参数的每个可能组合的一系列IF语句,我做这样的事情:

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.

这涵盖了null和非null参数的任何混合,而不会随着参数数量的增加而指数地增加IF语句的数量。

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:

这是我的完整猜测,但在Oracle中,您需要将游标声明为SP的IN OUT参数以与Crystal一起使用。 mySQL有同样的要求吗?以下是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...

#3

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

#1