I've found that you have to nest the WHEN ... in its own BEGIN ... END
block.

HTH,
Jason Wharton
CPS - Mesa AZ
http://www.ibobjects.com

----- Original Message -----
From: "Marcelo Miorelli" <jjkalki@...>
To: "Interbase Krishna Balarama" <ib-support@yahoogroups.com>
Sent: Friday, February 02, 2001 8:18 AM
Subject: [ib-support] exception


> Untitled-1Hello dear all.
> Suppose I have a simple table like this:
>
> TABLE PERSON:
> COD_PERSON INTEGER NOT NULL,
> DES_PERSON VARCHAR(40) NOT NULL;
>
> COD_PERSON IS THE PRIMARY KEY.
>
> and I create another unique index in the following way:
>
> create unique index SKPERSON on PERSON(DES_PERSON);
>
> till here everything fine.
> Now I get a stored procedure to insert and update records on this table.
> Something like this:
>
> CREATE PROCEDURE INPERSON (
> P1 integer,
> P2 VARCHAR(40),
> P3 VARCHAR(1)
> ) RETURNS (
> R1 integer
> ) AS
> begin
>
> r1 = 1;
> insert into person(cod_person,des_person) values(:p1,:p2);
>
> when SQLCODE -803 do begin /* 803 = violation of primary key
constraint
> */
>
> if (upper(p3) = 'Y') then begin
>
> update person
> set des_person = :p2
> where cod_person = :p1;
>
> end/*if*/
> else begin
>
> r1 = -1;
> exception PERSON_ALREADY_EXISTS;
>
> end/*else*/
> end/*when*/
> end
>
> Obs: Suppose this exception PERSON_ALREADY_EXISTS is already created, and
> the name of the person is unique only for the purpose of this example.
>
> I would like to put in this procedure another exception like "A person
with
> this name already exists in code %d".
> How can I do this?
> What is the SQLCODE?
>
> Thanks in advance,
> Marcelo Miorelli
>
>
>
> To unsubscribe from this group, send an email to:
> ib-support-unsubscribe@egroups.com
>
>
>