Search code examples
stored-proceduresdb2

DB2 Stored Procedure try catch

Hi i'm creating a database with DB2. I use IBM Data Client. I want to use try catch into my stored procedure but it seems that are not supported by DB2, can any one help me? I need to handle sql errors and to return its. How can i do that?

Solution 
  • CREATE OR REPLACE PROCEDURE sp_Applicazione_Aggiorna
(               
                IN @VAR1 INT,
                IN @VAR2 INT,
                IN @VAR3 VARCHAR(16),
                OUT @RETURNCODE INTEGER
)

LANGUAGE SQL
P1: BEGIN
DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND
SET @RETURNCODE = SQLCODE;



IF not exists (select VAR1 from DB.TABLEA where VAR1 = @VAR1)
THEN
    set @ReturnCode = 3011;
ELSE
    UPDATE          DB.TABLEA SET
                    VAR2=@VAR2,
                    VAR3=@VAR3
    WHERE VAR1=@VAR1;
                    
END IF;
END P1