hi ,,


how can i call a sql stored proc from a sql user defined function in db2.

i tried the following syntax but is not buliding

call StoredProcName();

please help asap


bye

Here is an example:

CREATE FUNCTION demo.udf1 (i_input VARCHAR(4000))
RETURNS VARCHAR(4000)
LANGUAGE SQL
NOT DETERMINISTIC
specific udf1
READS SQL DATA
d2: BEGIN ATOMIC
DECLARE status VARCHAR(4000);
CALL demo.status(i_input, status);
RETURN status;
END d2@

The demo.status Procedure is defined as:

CREATE PROCEDURE demo.status ( IN i_input VARCHAR(4000),
OUT OUTPUT VARCHAR(4000) )
LANGUAGE SQL
READS SQL DATA
BEGIN
.......

So as you can see, it is important to have matching types as well as declaring the output from the Procedure within the UDF.

Graham Martin
http://www-306.ibm.com/software/data/db2/migration/