Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2002
    Posts
    10

    Unanswered: stored procedure error

    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;

  2. #2
    Join Date
    Jun 2002
    Posts
    10
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •