Hi gurus ,

I have written the following code , but not able to trap the SQLCODE
, EVEN though the input is not proper i am not getting the proper
values of SQLCODE ...CAN ANYBODY TELL me how to trap the SQLCODE value in SP ?

trinmoy




CREATE PROCEDURE DB2ADMIN.Proc2 ( IN ll_P_id char(12),
OUT SQLCODE_OUT int )
RESULT SETS 1
LANGUAGE SQL
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
P1: BEGIN
-- Declare variables
DECLARE SQLCODE INT DEFAULT 0;

-- Declare cursor
DECLARE cursor1 CURSOR WITH RETURN FOR
SELECT A.PCATEGORY AS P_CATEGORY,
A.STATUS AS P_STATUS,
FROM PROJECT A
WHERE A.PRIMARYKEY = ll_P_ID ;

-- Declare handler
DECLARE EXIT HANDLER FOR SQLEXCEPTION
SET SQLCODE_OUT = 0;

-- Cursor left open for client application
OPEN cursor1;

END P1

Because the SET statement is a standard SQL , so it will return the SQLCODE = 0 , after finishing the SET statement the program exit!So you can not catch the SQLCODE of Exception!

You can try the following:
DECLARE EXIT HANDLER FOR SQLEXCEPTION;
VALUES (0) INTO SQLCODE_OUT;

-----------------------------------------------
sorry jerk its not working ..still i am not able to trap it ...i want to get the SQLCODE ...FOR EXAMPLE IF SQLCODE = 100 (NOT FOUND..NEED TO DO SOME PROCESSING ....

how to catch the SQLCODE OF the sql statement i am still unaware ....

hello gurus

i am not able to trap the SQLCODE Still...i might be doig something wrong ...but unware whts is it ...
i want to catch the SQLCODE FOR EXAMPLE if it is not found or may my for other cases

REATE PROCEDURE DB2ADMIN.Proc2 ( IN ll_P_id char(12),
OUT SQLCODE_OUT int )
RESULT SETS 1
LANGUAGE SQL
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
P1: BEGIN
-- Declare variables
DECLARE SQLCODE INT DEFAULT 0;

-- Declare cursor
DECLARE cursor1 CURSOR WITH RETURN FOR
SELECT A.PCATEGORY AS P_CATEGORY,
A.STATUS AS P_STATUS,
FROM PROJECT A
WHERE A.PRIMARYKEY = ll_P_ID ;

-- Declare handler
DECLARE EXIT HANDLER FOR SQLEXCEPTION
set SQLCODE_OUT = SQLCODE;

-- Cursor left open for client application
OPEN cursor1;

IF SQLCODE_OUT !=0 THEN
--- DO SOME PROSECCING ....;
ELSE
INSERT INTO SOME TABLE VALUES('11,'22');
COMMIT;
END IF ;

END P1


pls tell me how to trap the sqlcode for the above case ..and wht i am doing wrong ....

REATE PROCEDURE DB2ADMIN.Proc2 ( IN ll_P_id char(12),
OUT SQLCODE_OUT int )
RESULT SETS 1
LANGUAGE SQL
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
P1: BEGIN
-- Declare variables
DECLARE SQLCODE INT DEFAULT 0;

-- Declare cursor
DECLARE cursor1 CURSOR FOR
SELECT A.PCATEGORY AS P_CATEGORY,
A.STATUS AS P_STATUS,
FROM PROJECT A
WHERE A.PRIMARYKEY = ll_P_ID ;

-- Declare handler
DECLARE EXIT HANDLER FOR SQLEXCEPTION
set SQLCODE_OUT = SQLCODE;

-- Cursor left open for client application
OPEN cursor1;

IF SQLCODE <>0 THEN
--- DO SOME PROSECCING ....;
ELSE
INSERT INTO SOME TABLE VALUES('11,'22');
COMMIT;
END IF ;

END P1