Someone knows how can i call a procedure in a function body, i've been tried like this:

CREATE FUNCTION function1( p_dsc_ctx VARCHAR(7) ,
p_dsc_colunm VARCHAR(18))
LANGUAGE SQL
NOT DETERMINISTIC
MODIFIES SQL DATA
RETURNS VARCHAR(50)
BEGIN ATOMIC

DECLARE vReturn VARCHAR(50);

call procedure1( p_dsc_ctx, p_dsc_colunm , vReturn );

RETURN vReturn;
END

but this error occurs:

[IBM][CLI Driver][DB2/LINUX] SQL0628N Multiple or conflicting keywords involving the "MODIFIES SQL DATA" clause are present. LINE NUMBER=16. SQLSTATE=42613

Thanks

Calling SPs from UDF is not permitted for any version prior to 8.2.

Andy

But in version 8.2 is permited and my db2 version is 8.2, you had already do something like this?

Thanks

We are just now experimenting with 8.2 so we have not tried this yet. I looked at the grammar for CREATE FUNCTION because of the error message you posted. I think you need to move the RETURNS clause like this:

CREATE FUNCTION function1( p_dsc_ctx VARCHAR(7) ,
p_dsc_colunm VARCHAR(18))
RETURNS VARCHAR(50)
LANGUAGE SQL
NOT DETERMINISTIC
MODIFIES SQL DATA
BEGIN ATOMIC

DECLARE vReturn VARCHAR(50);

call procedure1( p_dsc_ctx, p_dsc_colunm , vReturn );

RETURN vReturn;
END

HTH
Andy

I tried to do what you said but the error message continue, i'll try to find a solution for this case, if you find one please tell me.

Thanks a lot.

What happens when you leave out the "MODIFIED SQL DATA" clause?

Andy

I think i found the reason of my problem, i put the same name for the procedure, function and the global temporary table that the procedure reads i think db2 don't know in this case what are the statement he'll call, i change the name of then and db2 permits me to compile then.

Thanks a lot.

Can you post you example, what did you do to achieve for calling Sp from UDF?

It is hard to believe that db2 will be confused when using same name for a routine ,a function and a table .. Each one of them is a different type of object and used in a different fashion ... So, it has to be something else ...

I have used UDFs and SPs with the same name before

Cheers
Sathyaram

My local installation of the documentation indicates that MODIFIES SQL DATA is "Valid only if RETURNS specifies a table (TABLE column-list)".

James Campbell