Trapping error inside stored procedure using ON EXCEPTION
Hi,
I want to write a stored procedure AddCategory() which will
return the category number if already existing and insert and
return the serial number otherwise. The category table is
create table category
(
catnum SERIAL NOT NULL,
catname CY (catnum),
UNIQUE (catname)
);
Can someone tell me whats wrong with this procedure? It does'nt
work and it won't recognize NULL in my IF statements!
-------------------------------------------------------------------------
-- Stored procedure, inserts values into category table if not existing
-- and returns category number
CREATE PROCEDURE AddCategory (name CHAR(30))
RETURNING INTEGER;
DEFINE num INTEGER;
TRACE ON;
ON EXCEPTION SET err_num
IF name = NULL THEN
LET num = NULL;
ELSE
SELECT catnum
INTO num
FROM category
WHERE catname = name;
IF err_num < 0 THEN
INSERT INTO catgory VALUES (0, name); LET num = DBINFO('sqlca.sqlerrd1');
END IF
END IF
END EXCEPTION WITH RESUME
RETURN num;
END PROCEDURE;
DOCUMENT 'This procedure returns the catnum after inserting/selecting
'value from category table.'
---------------------------------------------------------------------------
Thanks!
Vaishali Padhye
Triad Systems Corp.
#include <std_discliamer.h>