Will Calling a Procedure from user defined function work in DB2 v8 for Windows????

it throws the below error while i try to compile:
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "CALL TEMP_GET_INT_VARIABLE" was found following
"ALUE INT default 0; ". Expected tokens may include: "<compound_return>".
LINE NUMBER=7. SQLSTATE=42601


Example:
---------------------------------------------------------------------------
CREATE PROCEDURE TEMP_GET_INT_VARIABLE(p_v int)
RESULT SETS 0
LANGUAGE SQL
NOT DETERMINISTIC
BEGIN
DECLARE var int;
END
@

CREATE FUNCTION GET_INT_VARIABLE(P_VAR_NAME VARCHAR(2000))
RETURNS INT
LANGUAGE SQL
MODIFIES SQL DATA
BEGIN ATOMIC
DECLARE INT_VALUE INT default 0;
CALL TEMP_GET_INT_VARIABLE(1);
RETURN INT_VALUE;
END
@
---------------------------------------------------------------------------

appreciate any help regarding this problem....

Jake

You should be able to call a procedure from a UDF... I think your issue is actually that you can't have "MODIFIES SQL DATA" in a function unless it returns a table.

i think MODIFIES SQL DATA is not the reason... to make myself sure about it, i removed it and compiled the procedure once again... .the same error occurs... as far as i know MODIFIES SQL DATA tells the database that the function modifies data persisted in the database...

Jake

In the SQL Ref volume 2 under CREATE FUNCTION you'll note that "MODIFIES SQL DATA" has a foot note (1), and that footnote reads:

"Notes: 1 Valid only if RETURNS specifies a table (TABLE column-list)"

What version of db2 are you using? When I copy and paste your exact definitions I see this error:

DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0628N Multiple or conflicting keywords involving the "MODIFIES SQL DATA"
clause are present. LINE NUMBER=10. SQLSTATE=42613

It's happenig the same problem with me, if you find a solution please, tell us, my db2 version is the latest 8.2.

Thanks.

sorry for the late reply.... i'm using DB2 v8.1.3 for Windows

Jake