Bad Behavior Interpreting If Statements in Stored Procs

[NoahS]

We have a web page displaying certain data, using a stored procedure, and we wanted to make a crystal report that displays the same information. There are two types of datasets that can be returned by the SP, so there is an IF-ELSE block that, hinging on one parameter, will return one type or the other. This works fine for the web page, and also works fine in the Crystal Report when in Crystal Designer. The problem comes from using the Crystal Report in the web viewer. We use the ActiveXViewer. Basically, for some reason, it will not evaluate the if statements properly. The code is in the following form:

/* This IF block determines whether a wildcard should be used */
IF (@param) = 'all'
BEGIN
SET @temp = '%'
END
ELSE
BEGIN
SET @temp = @param
END

/* This is the IF block that doesn't work */
IF @temp = '%'
BEGIN
--Query type 1
END
ELSE
BEGIN
--Query type 2
END

There are other parameters that are checked to convert 'all' to '%' also, but they do not effect the IF statement. I have run various checks to make sure that (the first) if statement works correctly. For the second IF, I have tried using another IF statement instead of an ELSE statement. The really weird part is that if I artificially force the first IF block, it works in the web viewer (for type 1 queries of course). E.G.- if I say:

IF (@temp='%' OR 1=1)
...

or

IF (@param) = 'all'
BEGIN
SET @temp = '%'
END
ELSE
BEGIN
--SET @temp = @param
SET @temp = 'DEBUG'
END

IF (@temp='%' OR @temp='DEBUG')
...

the report works. And if I use the second method to force the first IF block... @temp IS '%', as it should be. I've tried doing the switch on @param (rather than @temp) but it makes no difference. Keep in mind that through all of this, the stored procedure behaves as expected in the web page and in crystal reports, the bad behavior only appears in the crystal reports web view.

We are using Crystal Reports 9 and SQL Server 2000. We use builder asp pages to get the parameters passed from selection pages to the report web viewer, and to set the database location. Please ask if you want any more details about any of this (or anything else for that matter).

We've theorized that the crystal reports web viewer must use some different format to talk to the database, or have some different flags set. The other thing I thought of, is it seems like it might be trying to precache results somehow, or something like that. Messing with execution time in some wat. Please let me know if you know anything about these things, or have any pointers on what to look at, where to look, or some other resources online to look into. I have already searched the Business Objects Knowledge Base, but it is of course possible that I missed something, or didn't know what to look for.

Thanks,
Noah

 

[synapsevampire]

Sicne you're NOT sharing the entire SP, I won't bother trying to debug.

But I would suggest that you post in a SQL Server forum, and that you include the code, and that you forego using a % and instead use a different Select which merely omits that part of the where clause, if that's the intent as it's a bit cleaner.

-k

 

[NoahS]

Is there a reason why you think the problem is at the SQL Server level? It would be helpful to me to know what that is. I had assumed that it was not, since the stored procedure works in Query Analyzer, in the ASP page that uses it, and in the Crystal Reports 9 application. I was thinking the problem must be something having to do with the Crystal Reports web viewers and the way they communicate with the DB, or at least the way that we have implemented the communication with the DB. I wasn't sure if this should be in Data Access or Integration, and didn't want to crosspost. Let me know if Integration is a better match.