Stored Proc Question

[chesl73]

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

 

[Mercury2]

Use a PREPARE stm first.

...
DECLARE SQLSTM  VARCHAR (2048);
SET SQLSTM = 'SELECT * FROM TABLE1 WHERE ' || ADDWHERE; 
PREPARE S1 FROM SQLSTM;
DECLARE C1 SURSOR FOR S1;
OPEN C1;
...