Hi all,

I would like to write a stored proc that takes a IN parameter(s) and then uses this to dynamically change the WHERE clause of my sql.
example is something like:

CREATE PROCEDURE BLAH(IN MYVAR VARCHAR(50),IN MYVAL VARCHAR(50))

....
....
DECLARE ADDWHERE VARCHAR(100);
IF MYVAR='VIEWCAT' THEN
SET ADDWHERE = 'TABLE1.FIELD1='''|| MYVAL || '''';
ELSE IF MYVAR='VIEWDOG' THEN
SET ADDWHERE = 'TABLE1.FIELD2='''|| MYVAL || '''';
END IF;

DECLARE C1 CURSOR FOR
'SELECT * FROM TABLE1 WHERE ' || ADDWHERE;

----------

Is this possible? I've tried and can't get it to work. Not sure that the single quotes around the SELECT are correct and if I'd need to use some kind of PREPARE statement - just don't know. Any ideas?


Thanks

You will need to set it up using dynamic SQL. You will need to set a varchar variable to the entire SQL statement you want to execute. Then you will prepare it and execute it. There are numerous examples. Look in the documentation on PREPARE and OPEN.

Andy

Here's an example ---

After all your declare statements including
DECLARE stmt VARCHAR(1000);-- give something like this :

SET stmt = 'SELECT val FROM SESSION.TmpValues WHERE id = ?
ORDER BY readingval DESC FETCH FIRST 3 ROWS ONLY ';

SET hasNext = 1;
PREPARE s1 FROM stmt;
BEGIN
DECLARE totalDateTime TIMESTAMP;

DECLARE c1 CURSOR FOR s1;
OPEN c1 USING sid;
FETCH c1 INTO totalDateTime;

WHILE hasNext = 1 DO

SET endTime = totalDateTime;
SET startTime = endTime - 1 DAY;

CALL COLLECT_DATA(param1, param2, param3 );
FETCH c1 INTO totalDateTime;
END WHILE;
CLOSE c1;
END;

The stored proc is to be used for a Crystal Report so I just want to return a RESULTSET, not FETCH the results or anything like that. I want to put the result of the query into a cursor and then open the cursor.
I tried this but it didn't work:

CREATE PROCEDURE BLAH(IN MYVAR VARCHAR(50),IN MYVAL VARCHAR(50))

....
....
DECLARE ADDWHERE VARCHAR(100);
DELCARE SMT VARCHAR(1000);

IF MYVAR='VIEWCAT' THEN
SET ADDWHERE = 'TABLE1.FIELD1='''|| MYVAL || '''';
ELSE IF MYVAR='VIEWDOG' THEN
SET ADDWHERE = 'TABLE1.FIELD2='''|| MYVAL || '''';
END IF;

SET SMT = 'SELECT * FROM TABLE1 WHERE ' || ADDWHERE;

PREPARE S1 FROM SMT;
DECLARE C1 CURSOR FOR S1;

OPEN C1;
-----

Any reason why this won't work?

Thanks again.

Two things --
Change ELSE IF to ELSEIF (one word)

and secondly change the cursor part to this --

PREPARE S1 FROM SMT;

BEGIN
DECLARE C1 CURSOR FOR S1;
OPEN C1;
END;

It should work now..

This is an answer to the OP which I posted on "Developers work for Linux, UNIX, and Windows".

Yes tonkuma, thanks for that. Looks a lot easier.
Was also just interested in how to do it using PREPARE statement.

DB2user - thanks again.
One final thing, are you saying that I remove the IF THEN ELSEIF section from the BEGIN - END ?

ie, something like:


CREATE PROCEDURE BLAH(IN MYVAR VARCHAR(50),IN MYVAL VARCHAR(50))

DECLARE ADDWHERE VARCHAR(100);
DELCARE SMT VARCHAR(1000);

IF MYVAR='VIEWCAT' THEN
SET ADDWHERE = 'TABLE1.FIELD1='''|| MYVAL || '''';
ELSEIF MYVAR='VIEWDOG' THEN
SET ADDWHERE = 'TABLE1.FIELD2='''|| MYVAL || '''';
END IF;


SET SMT = 'SELECT * FROM TABLE1 WHERE ' || ADDWHERE;

--ALL OF ABOVE BEFORE THE BEGIN-END?

BEGIN
PREPARE S1 FROM SMT;
DECLARE C1 CURSOR FOR S1;
OPEN C1;
END;

You just need to put the cursor declaration in its own BEGIN/END BLOCK...so your final sproc should look like this..



CREATE PROCEDURE BLAH(IN MYVAR VARCHAR(50),IN MYVAL VARCHAR(50))
RESULT SETS 1
MODIFIES SQL DATA
NOT DETERMINISTIC
LANGUAGE SQL

BEGIN

DECLARE ADDWHERE VARCHAR(100);
DELCARE SMT VARCHAR(1000);

IF MYVAR='VIEWCAT' THEN
SET ADDWHERE = 'TABLE1.FIELD1='''|| MYVAL || '''';
ELSEIF MYVAR='VIEWDOG' THEN
SET ADDWHERE = 'TABLE1.FIELD2='''|| MYVAL || '''';
END IF;


SET SMT = 'SELECT * FROM TABLE1 WHERE ' || ADDWHERE;

PREPARE S1 FROM SMT;

--starting another block here
BEGIN
DECLARE C1 CURSOR FOR S1;
OPEN C1;
END;

END
@

--- then just give this command to create the procedure...
db2 -td@ -vf filename.sql