Subject Help!!! : Error Handling
Author Arvee
I use WHEN statement to check SQLCODE but if seems not to work.
When executed the SP, it omit all statements in the body to end. This is
the SP I wrote. Could anyone check it and tell me what's wrong?

/* SP Insert New Product */
CREATE PROCEDURE TDS_TPRODUCT_INSERT_1 (
PROD_CD CHAR (6),
PRODNAME CHAR (30),
CURRENCY_TX CHAR (3),
PRODDESC CHAR (16),
GRP_SIZE_NB FLOAT,
FK1_COMPANY_CD CHAR (6),
FK2_PRODUCT_CD CHAR (6))
RETURNS (
ERRNO SMALLINT)
AS
/* RETURN CODES:
-- 0 - SUCCESS
-- 10 - COMPANY CODE NOT FOUND
-- 20 - INSERT PRODUCT FAILED
*/
declare variable lcl_prod_id char(6);
declare variable lcl_fk_company_id char(6);
declare variable lcl_fk_prod_id char(6);
declare variable fprod integer;
declare variable ccomp integer;
BEGIN
lcl_prod_id = upper(:PROD_CD);
lcl_fk_prod_id = UPPER(:FK2_PRODUCT_CD);
lcl_fk_company_id = UPPER(:FK1_COMPANY_CD);
if (lcl_fk_prod_id is not null) then
begin
SELECT count(*) FROM TPRODUCT TP
WHERE TP.PROD_CD =:lcl_fk_prod_id
into :fprod;
IF (fprod <1) then
begin
ERRNO = 20;
exit;
end
end
SELECT count(*) FROM TCOMPANY
WHERE COMPANY_CD =:lcl_fk_company_id
into :ccomp;
IF (ccomp = 0) then
begin
ERRNO = 30;
exit;
end
/* Insert New Product. */
INSERT INTO TPRODUCT

(PROD_CD,PRODNAME,CURRENCY_TX,PRODDESC,GRP_SIZE_N
B,
UPDATED_UID,UPDATED_TSP,NOTE_CD,FK1_COMPANY_CD,
FK2_PRODUCT_CD)
VALUES( :lcl_prod_id,
:PRODNAME,
:CURRENCY_TX,
:PRODDESC,
:GRP_SIZE_NB,
current_user,
CURRENT_TIMESTAMP,
NULL,
:lcl_fk_company_id,
:lcl_fk_prod_id);
ERRNO=0;
SUSPEND;
/* Error Handling */
WHEN SQLCODE -803 DO
BEGIN
ERRNO = 20;
EXIT;
END
END
========================================================

Regards,
Arvee