Hello

I am struggling with a single Stored Procedure that I would like to be able to return a result set that reflects any of the parameters. In the UI, the user can select any ClientID (or all), any UserID (or all), a begin date and\or an end date. My problem is that I don't understand the use of the params listed in the "OPEN Result USING ClientID, UserID, InvDate1, InvDate2;" line.

That line appears to be an incorrect assumption by me, as only the first parameter functions properly. How would a skilled SQL jock handle this situation?

CREATE PROCEDURE "SPGetInv" (IN "ClientID" INTEGER, IN "UserID" INTEGER,
IN "InvDate1" DATE, IN "InvDate2" DATE) -- any combination of parameters may be used
BEGIN                                   
DECLARE Result CURSOR WITH RETURN For stmt;
DECLARE AndClauses VARCHAR DEFAULT '';
DECLARE SQLStatement VARCHAR DEFAULT
'SELECT * FROM Invoice WHERE (1 = 1)';
IF ClientID > 0 THEN
 SET AndClauses = AndClauses + ' AND "P"."ClientID" = ?';
END IF;
IF UserID > 0 THEN
 SET AndClauses = AndClauses + ' AND "P"."UserID" = ?';
END IF;
IF InvDate1 > DATE '2000-01-01' THEN
 SET AndClauses = AndClauses + ' AND "P"."RequestDate" > ?';
END IF;
IF InvDate2 > DATE '2000-01-01' THEN
 SET AndClauses = AndClauses + ' AND "P"."RequestDate" < ?';
END IF;
SET SQLStatement = SQLStatement + AndClauses +
' GROUP BY InvoiceID";';
PREPARE Stmt FROM SQLStatement;
OPEN Result USING ClientID, UserID, InvDate1, InvDate2; -- params might all be NULL or any combination
END
VERSION 1.00!

Don't worry about hurting my feelings

Regards

Peter

Peter,

I don't think this procedure can work at all:

1. What is "P". ? That will raise an error for sure.
2. Why the quotes after the "GROUP BY InvoiceID" clause? Also this will
raise an error.

Apart from that: which result are you expecting if you don't pass all
parameters?

Hint: In order to debug a procedure you can write a script, hardcode the
params and set breakpoints or log messages like this:
SET Log message to SQLStatement;

Uli

Peter


If any of the parameters are left out, or a null is passed in you get a null. How that is handled depends on how you have StandardNullBehaviour set. You may need to take account of that in your tests.

Ignoring that you're wasting your time using ? in your statement. Since you're building the code then just build it ie

SET AndClauses = AndClauses + ' AND "P"."ClientID" = ?';

becomes

SET AndClauses = AndClauses + ' AND "P"."ClientID" = '+CAST(ClientID AS VARCHAR);

In my opinion it becomes more readable and you're doing basically what parameter substitution would do anyway. You also have to decide what a missing / null parameter actually means - does it mean ignore it or does it mean select where its null in the table so you could have either of the following two

IF ((ClientID IS NOT NULL) AND ClientID > 0) THEN
 SET AndClauses = AndClauses + ' AND "P"."ClientID" = ?';
END IF;

or

IF (ClientID IS NOT NULL) AND ClientID > 0) THEN
SET AndClauses = AndClauses + ' AND "P"."ClientID" = '+CAST(ClientID AS VARCHAR);;
ELSEIF ClienetID IS NULL THEN
SET AndClauses = AndClauses + ' AND "P"."ClientID" IS NULL';
END IF;



Roy Lambert

Peter

You may well want to write preparatory code to catch nulls and set them to some other useful value at the start:

IF ClientID is NULL THEN
 SET ClientID = 1;
 END IF;

Or it may even be that if a value is null that whole section of the WHERE Clause might need to be removed, i.e.

IF ClientID IS NULL THEN
 SET ClientIDWhere = ''
 ELSE
 SET ClientIDWHERE = ' WHERE ClientID = '+CAST(ClientID as VARCHAR)+ ' ';
 END IF;

Prepare Stmt FROM
SelectPortion+
ClientIDWhere+
... other bits

Of course if this happens you will have to figure out whether the different bits of the WHERE clause need to start with a WHERE or an AND ...

Hi

Uli: I fibbed about it being the actual Stored Procedure - I had 'edited' my question to remove all the JOINS and extraneous details. The "P" reference made no sense in the code I posted, so I'm sorry I wasted your time. Thanks for the tip re the LOG. I've never used that method and it appears I should.

Roy and Adam: I followed Roy's suggestion and built the string dynamically, leaving out the silly ? references which would never have lined up ordinally, and...

 SET AndClauses = AndClauses + ' AND "P"."RequestDate" >= DATE ' +
  QUOTEDSTR(CAST(InvDate1 AS VARCHAR(10)));
  
... works perfectly, as do the other parameters. Thanks for your help, people, it is much appreciated.

Regards

Peter