Hi All,

I am writing an User Defined function. I am trying to handle exceptions, but unable to do so. I have few queris:
1.)Is exception handling is possible in DB2 UDF.
2)If possible how can I wirte a WHEN NO DATA FOUND excption(oracle's eqivalent) in db2.
3.)Can anyone please figure out where I am missing in the following code :

CREATE FUNCTION DOM9OCT.xyz( lo_sal integer )

RETURNS INTEGER

LANGUAGE SQL

READS SQL DATA

------------------------------------------------------------------------

-- SQL UDF (Scalar)

------------------------------------------------------------------------

F1: BEGIN ATOMIC

declare l_var integer;

declare l_flag char(1);

DECLARE GENERATEQUERYEXCEPTION CONDITION FOR SQLSTATE 'OR000';

DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING

BEGIN

-- SET l_var=5;

END;

set l_flag='N';

set l_var=0;

while l_flag='N' do

Select PARENTENTITYID as l_var from umlcompositestate where compositestateid = l_var and modelid =2 and parententitytype = 121;

end while;

--end;

RETURN l_var;

END





Thanks

test this:

Select PARENTENTITYID into l_var from umlcompositestate where compositestateid = l_var and modelid =2 and parententitytype = 121;

Hi,

The select into clause doesnot work inside a function.
Secondly , I wanted to know whether exception handling is possible in udfs and if yes then how can I do that. In my code snippet that I have posted, I am getting errors while handling exceptions. whereas similar things work in procedures.

Thanks,
Raj

can I do a select into clause in an UDF. Its giving me error.How to do that.

Raj,
You cannot use the SELECT INTO, but you can do:

SET l_var = (Select PARENTENTITYID from umlcompositestate where compositestateid = l_var and modelid =2 and parententitytype = 121);

HTH

Andy