hi,

I get 'syntax error' when trying to compile this stored procedure. I tried different combinations with semicolons, without semicolons but no success:

CREATE PROCEDURE read_part( p_id INT )
RETURNING INT, VARCHAR(20), VARCHAR(20), LVARCHAR, VARCHAR(255);

DEFINE sql_num INT;
DEFINE isam_num INT;
DEFINE sql_err VARCHAR( 250 );

DEFINE l_id LIKE t_part.partid;
DEFINE l_name LIKE t_part.partname;
DEFINE l_num LIKE t_part.partnum;
DEFINE l_desc LIKE t_part.description;
DEFINE l_comm LIKE t_part.comment;
DEFINE l_ret INT;

ON EXCEPTION SET sql_num, isam_num, sql_err
RAISE EXCEPTION sql_num, isam_num, sql_err;
END EXCEPTION

IF p_id = 0 THEN
FOREACH
SELECT partid, partname, partnum, description, comment
INTO l_id, l_name, l_num, l_desc, l_comm
FROM t_part;

LET l_ret = l_id;

IF l_name IS NULL THEN
LET l_name = '';
END IF;
IF l_num IS NULL THEN
LET l_num = '';
END IF;
IF l_desc IS NULL THEN
LET l_desc = '';
END IF;
IF l_comm IS NULL THEN
LET l_comm = '';
END IF;

RETURN l_ret, l_name, l_num, l_desc, l_comm WITH RESUME;

END FOREACH;
ELIF p_id > 0 THEN
SELECT partid, partname, partnum, description, comment
INTO l_id, l_name, l_num, l_desc, l_comm
FROM t_part
WHERE partid = p_id;

LET l_ret = l_id;

IF l_name IS NULL THEN
LET l_name = '';
END IF
IF l_num IS NULL THEN
LET l_num = '';
END IF;
IF l_desc IS NULL THEN
LET l_desc = '';
END IF;
IF l_comm IS NULL THEN
LET l_comm = '';
END IF;

RETURN l_ret, l_name, l_num, l_desc, l_comm;
ELSE
RAISE EXCEPTION -746, 0, 'DEBUG: Invalid input - ';
END IF;

END PROCEDURE;

amazing....

it seems first SELECT must not end with semicolon and second
SELECT must. Completelly illogical syntax and I am sure in a month or
two I'll have the same problem again