How To Call Procdure From Function In DB2

[pkaival]

i want to call procedure from function In DB2

i create function but it is giving an error like
--------------------------------------------------------------------------------------------------------------------
"DB2 SQL error: SQLCODE: -374, SQLSTATE: 428C2, SQLERRMC: MODIFIES SQL DATA;GetV_2
The "MODIFIES SQL DATA" clause has not been specified in the CREATE FUNCTION statement for LANGUAGE SQL function "GetV_2" but an examination of the function body reveals that it should be specified."
--------------------------------------------------------------------------------------------------------------------

CREATE FUNCTION "GetV_2" (v_1 varchar(20)) RETURNS varchar(1000)
LANGUAGE SQL
BEGIN ATOMIC

DECLARE v_2 varchar(1000);

call GetProc(v_1,v_2);
return v_2;

END
-------------------------------------------------------------------------------------------

Please help me to find the solution
Thanks in Advance....

[sakumar9]

Hi,

Add MODIFIES SQL DATA clause to your CREATE FUNCTION statement. I can give you an example:

CREATE FUNCTION test ()
RETURNS TABLE (a INTEGER)
LANGUAGE SQL
MODIFIES SQL DATA
NO EXTERNAL ACTION
DETERMINISTIC
BEGIN ATOMIC
INSERT INTO a VALUES (1);
RETURN
SELECT a
FROM a;
END @


Let me know if you still run into problems.

Thanks & regards
--- Sanjay