Dixon,

> Ive written the following to get a number, but if the row in the
> lookup table hasn't been created yet, I want to create a new record
> and return a default value of 1.
>
> SET TERM !!

SET TERM !!; /* you need a semicolon after setting a new terminator! */

> CREATE PROCEDURE GETPAYNUM RETURNS (hightn INTEGER) AS
> BEGIN
> UPDATE LUTABLE SET LU_RATE=LU_RATE +1 WHERE LU_FIELD='PYNUM';
> FOR SELECT LU_RATE FROM LUTABLE WHERE LU_FIELD='PYNUM' INTO hightn

INTO :hightn /* if yous elect INTO variabnles,
they have to be prefices with
a colon (:)
This is also the case when you use
them in an insert statment - otherwise
they'd be treted as fieldnames */

> DO

DO BEGIN

/* if you have more than one statement you need DO BEGIN ... END */

> IF (LU_RATE IS NULL) THEN
> BEGIN
> INSERT INTO LUTABLE (LU_DESC, LU_FIELD, LU_RATE, LU_ORDER,
> LU_DEF)
> VALUES ('PayNumber', 'PYNUM', 1, 0, 'N');
>
> SELECT LU_RATE FROM LUTABLE WHERE LU_FIELD='PYNUM' INTO
> hightn;

/* again: INTO :hightn */
/* CAUTION!
if you have more than one record where
LU_FIELD = 'PYNUM'
you'll get a:
MULTIPLE RECORDS in SINGLETON SELECT
error. SELECT ... into camn't handlöe more
than one resturning record!
*/

> END
> SUSPEND;
> END!!
> SET TERM ;

SET TERM ;!!


So the complete sp should read sth. like:

SET TERM !!;
CREATE PROCEDURE GETPAYNUM RETURNS (hightn INTEGER) AS
BEGIN
UPDATE LUTABLE SET LU_RATE=LU_RATE +1 WHERE LU_FIELD='PYNUM';

FOR SELECT LU_RATE
FROM LUTABLE WHERE LU_FIELD='PYNUM'
INTO :hightn
DO BEGIN
IF (LU_RATE IS NULL) THEN
BEGIN
INSERT INTO LUTABLE
(LU_DESC, LU_FIELD, LU_RATE, LU_ORDER, LU_DEF)
VALUES ('PayNumber', 'PYNUM', 1, 0, 'N');
hightn = 1;
END
SUSPEND;
EXIT; /* if you want to return just one record */
END
END!!
SET TERM ;

Hth

Luc.