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