At 08:47 AM 11-09-02 +0000, you wrote:

>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?

Mainly, if an SQL error occurs, it will jump immediately out to your WHEN
sequence and execute no more statements because your WHEN sequence
terminates the program. You can only go back and continue executing if
your WHEN block swallows the error instead of calling EXIT.

WHEN SQLCODE is rather too broad-brush to be useful for many
exception-handling scenarios. You should use WHEN GDSCODE.. or WHEN
MyCustomException to handle specific exceptions (i.e. silence them and
write a log record, for example). You'll find the GDSCODES (in the "Error
Code" column) in the Error Status Array Codes section of the LangRef
manual, but remove the 'isc_' prefix from them.

Logically, also, there is a problem here. You aren't initialising the
ERRNO variable so, unless the specific conditions occur, ERRNO gets no value.

Another point: don't use SELECT COUNT(*) to determine the existence of a
row. SQL has the EXISTS() predicate for that.

/* 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
ERRNO=20; // ensures that everything but success will return failure
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
if (exists (
SELECT 1 FROM TPRODUCT
WHERE PROD_CD =lcl_fk_prod_id) ) then
begin
ERRNO = 20;
exit;
end
if (not exists (
SELECT 1 FROM TCOMPANY
WHERE COMPANY_CD =lcl_fk_company_id ) ) then
begin
ERRNO = 30;
exit;
end
/* Insert New Product. */
INSERT INTO TPRODUCT ...etc
....
ERRNO=0;
/* SUSPEND; remove this, doesn't belong here */
END

If you want to read up on exception handling, download the Tech Info sheet
on the subject from http://www.ibobjects.com/TechInfo.html It has a
Delphi + IBO focus but does have a section on using WHEN GDSCODE.

heLen