Hi!

I am still trying to solve the problem with varying SQL quesries in a stored
procedure, depending on the SP parameters (the very same problem which
appeared recently in "Optimalisation issues" thread). After reading the
documentation thoroughly, it seems to me a good approach would be something
along the lines of:

CREATE PROCEDURE get_data(
param1 INTEGER,
param2 INTEGER
) AS
DECLARE params_all CURSOR FOR SELECT key FROM table WHERE param1=:param1
AND param2=:param2;
DECLARE params_1 CURSOR FOR SELECT key FROM table WHERE param1=:param1;
DECLARE params_2 CURSOR FOR SELECT key FROM table WHERE param2=:param2;
DECLARE params_none CURSOR FOR SELECT key FROM table;
DECLARE VARIABLE key INTEGER;
BEGIN
-- Open the correct cursor
IF (param1 IS NULL) THEN
IF (param2 IS NULL) THEN
OPEN params_none;
ELSE
OPEN params_2;
ELSE
IF (param2 IS NULL) THEN
OPEN params_1;
ELSE
OPEN params_all;
WHILE (SQLCODE = 0) DO
BEGIN
-- Fetch data from the correct cursor
IF (param1 IS NULL) THEN
IF (param2 IS NULL) THEN
FETCH params_none INTO :key;
ELSE
FETCH params_2 INTO :key;
ELSE
IF (param2 IS NULL) THEN
FETCH params_1 INTO :key;
ELSE
FETCH params_all INTO :key;
-- Do something with the fetched data
END
-- Close the cursor
END

Probably storing SQLCODE would be necessary after the fetch, because it will
get changed by further statements. That I can handle. The problem is that I
somehow can't get cursors to work in a stored procedure. I have never
actually used cursors before, so I have little idea what to do with them,
and all examples in the documentation deal with using cursors from a host
language, not from a stored procedure. Any working demonstration (even
something simple, like replacing FOR SELECT something FROM table INTO
:variable with cursors running in a WHILE loop rather than a FOR loop) would
be welcome.

Thanks, Pepak