Sql storedprocedure return no error code

[sajidk]

Hi All,

I am very new to DB2 programming. I have written a SQL stored procedure and called it from C# . The program is working but it is not returning the error code and sqlstate code. If i comment out all the code except the DELETE SQL statement it gives error with code "SQL0532" in my program side.
Why the EXIT handler is not invoking? OR Why it is not returning values through OUT parameters.?

Looking forward to hear from you. My stored procedure is given below.I am using iSeries Navigator for writing the the following code so some auto generated is also there.(line 5-17)

CREATE PROCEDURE TYKDFLIB.SP_DELETE ( 
    IN PID CHAR(20) , 
    OUT PSQLSTATE CHAR (5),
    OUT PSQLCODE INTEGER ) 
    LANGUAGE SQL 
    SPECIFIC TYKDFLIB.SP_DELETE 
    NOT DETERMINISTIC 
    MODIFIES SQL DATA 
    CALLED ON NULL INPUT 
    SET OPTION  ALWBLK = *ALLREAD , 
    ALWCPYDTA = *OPTIMIZE , 
    COMMIT = *NONE , 
    DECRESULT = (31, 31, 00) , 
    DFTRDBCOL = *NONE , 
    DYNDFTCOL = *NO , 
    DYNUSRPRF = *USER , 
    SRTSEQ = *HEX   
    BEGIN 
 
        DECLARE SQLSTATE CHAR ( 5 ) DEFAULT '00000' ;
          DECLARE SQLCODE INT DEFAULT 0 ;  
 
        DECLARE EXIT HANDLER FOR SQLEXCEPTION  
 
        SELECT SQLSTATE , SQLCODE  
        INTO PSQLSTATE , PSQLCODE  
        FROM SYSIBM . SYSDUMMY1 ; 
 
        DELETE FROM TYKDFLIB . TEST01 WHERE ID = PID ;    
 
        VALUES ( SQLSTATE , SQLCODE )  
        INTO PSQLSTATE , PSQLCODE ;
 
        END  ;
 

Thanks in advance!

[zenworks]

The following Stored procedure is working fine for me.

CREATE PROCEDURE TYKDFLIB.SP_DELETE (
IN PID CHAR(20) ,
OUT PSQLSTATE CHAR (5),
OUT PSQLCODE INTEGER )
LANGUAGE SQL
SPECIFIC TYKDFLIB.SP_DELETE
NOT DETERMINISTIC
MODIFIES SQL DATA
CALLED ON NULL INPUT
BEGIN

DECLARE SQLSTATE CHAR ( 5 ) DEFAULT '00000' ;
DECLARE SQLCODE INT DEFAULT 0 ;

DECLARE EXIT HANDLER FOR SQLEXCEPTION

SELECT SQLSTATE , SQLCODE
INTO PSQLSTATE , PSQLCODE
FROM SYSIBM . SYSDUMMY1 ;

VALUES ( SQLSTATE , SQLCODE )
INTO PSQLSTATE , PSQLCODE ;

END