Hello,

I have a function which tries to call a procedure. But for some reason I'm getting the error SQL0374N The "MODIFIES SQL DATA" clause has not been specified in the CREATE FUNCTION statement for LANGUAGE SQL function "myschema.my_func". When I put the clause I'm getting SQL0628N Multiple or conflicting keywords involving the "MODIFIES SQL DATA" clause are present. How could I fix it? Here are my function and procedure definitions :

CREATE PROCEDURE myschema.my_proc(IN id INTEGER, IN F_DATE DATE, OUT res VARCHAR(7))
P1: BEGIN
DECLARE rs_atr char(4);
DECLARE rs_dstart date;
DECLARE rs_dstop date;
DECLARE eors INT DEFAULT 0;
DECLARE not_found CONDITION FOR SQLSTATE '02000';
DECLARE rowVar CURSOR FOR select ...
from ...
where ...;
DECLARE CONTINUE HANDLER FOR not_found SET eors = 1;

OPEN rowVar;
FETCH rowVar INTO rs_dstart, rs_dstop, rs_atr;

SET res = '-------';

WHILE eors = 0 DO
IF RTRIM(rs_atr) = 'KD' THEN
SET res = '1234567';
ELSEIF RTRIM(rs_atr) = 'B' THEN
SET res = LEFT(res, 5) || '67';
ELSEIF RTRIM(rs_atr) = 'D' THEN
SET res = '12345' || RIGHT(res, 2);
ELSEIF (RTRIM(rs_atr) >= '1' AND RTRIM(rs_atr) <= '7') THEN
SET res = LEFT(res, INT(RTRIM(rs_atr)) - 1) || RTRIM(rs_atr) || RIGHT(res, 7 - INT(RTRIM(rs_atr)));
END IF;
FETCH rowVar INTO rs_dstart, rs_dstop, rs_atr;
END WHILE;

CLOSE rowVar;
END P1

CREATE FUNCTION myschema.my_func(ID integer, dateStr varchar(50))
RETURNS VARCHAR(7)
F1: BEGIN ATOMIC

DECLARE result varchar(7);
DECLARE f_date date;

SET f_date = date(dateStr);

call myschema.my_proc(ID, f_date, result);
RETURN result;
END

Thanks in advance

If you read the manual for CREATE FUNCTION, the clause MODIFIES SQL DATA is only valid for functions returning a table, not for a scalar function like yours.

Andy

I've put this clause only because I've got an error message. Anyway I still don't know how to fix this problem.

Which DB2 version and OS are you using?

Andy

DB2 ver. 8.1.15. Enterprise Server Edition

OS: Win XP Pro

I think you are out of luck with this method. Maybe if you changed the stored procedure to a UDF it might work.

Andy

Create the procedure using the READS SQL DATA or CONTAINS SQL clauses. Your procedure (as shown above) doesn't do data modifications, so this should be sufficient. Once the procedure is READS SQL DATA/CONTAINS SQL, you can create your UDF as-is.

Yes, that did the trick. Thank you!