Hi Martijn,

It's still the same. No responses. Do you have any suggestions?

Arvee

--- In ib-support@y..., "Martijn Tonies" <m.tonies@u...> wrote:
> Hi Arvee,
>
> what happens if you remove the SUSPEND -that should only be used
> for SELECTable procedures.
>
>
> With regards,
>
> Martijn Tonies
> InterBase Workbench - the developer tool for InterBase
> http://www.upscene.com
>
> Firebird Workbench - the developer tool for Firebird
> http://www.upscene.com
>
> Upscene Productions
> http://www.upscene.com
>
> "This is an object-oriented system.
> If we change anything, the users object."
>
>
> > 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