CODEWITHSUNDEEP
stored-proceduresdb2
Niccolò Cometto
Niccolò Cometto

Reputation: 53

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?

Upvotes: 0

Views: 7263

Answers (2)

Niccolò Cometto
Niccolò Cometto

Reputation: 53

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

Upvotes: 1

mao
mao

Reputation: 12267

DB2 LUW supports exception handlers (continue handlers, or exit handlers) for SQL PL procedures. Look in the DB2 Knowledge Center for your version for all the details. You can use them alongside conditions. You can have multiple handlers if you need specific processing. There are plenty of sample SQL PL procedures in both the Knowledge Center and in the DB2 LUW installed product directories.

Upvotes: 1

Related Questions