Error in mysql bigint variable declaration inside custom nextval function

Question

I forget put delimiter directive, some semicolon and was using tsl syntax like [select variable = field] that is not valid in mysql.
Mysql error when you use tsl syntax is [not allowed to return a result set from a function] and dont help much.
@AndreKR point all of it to me, thanks.
Im using mysqlworkbench 5.2.30 CE. The work function become:

delimiter //
CREATE FUNCTION nextval (seq_name varchar(100))  
  RETURNS bigint(20)  
    READS SQL DATA  
  NOT DETERMINISTIC  
    BEGIN  
     DECLARE workval bigint(20);  
     SELECT count(1) into workval  
        FROM tip_sequence  
        WHERE sequencename = seq_name;  
     IF workval <> 1 THEN  
        DELETE  
            FROM tip_sequence  
            WHERE sequencename = seq_name;  
        INSERT  
            INTO tip_sequence (sequencename, sequenceval, sequencestep)  
            VALUES (seq_name, 1, 1);  
     END IF;
     SELECT sequenceval into workval  
        FROM tip_sequence  
        WHERE sequencename = seq_name;  
     UPDATE tip_sequence  
        SET sequenceval = sequenceval + sequencestep  
        WHERE sequencename = seq_name;  
     RETURN workval;
    END//
delimiter ;

@Mitch Wheat The version is [Server version: 5.1.53-community MySQL Community Server (GPL)] — newway
– newway, Commented Nov 8, 2011 at 23:58

AndreKR · Accepted Answer · 2011-11-09 00:45:07Z

0

Since the DECLARE workval bigint(20); line is the first one with a semicolon at the end, I suspect you forgot to change the delimiter before inputting the function code (though this depends on the client you're using).

Try changing your code to:

DELIMITER #
CREATE FUNCTION nextval (seq_name varchar(100))  

...

END#

answered Nov 9, 2011 at 0:45

AndreKR

34k21 gold badges120 silver badges181 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

newway Over a year ago

Thanks a lot, its solve that problem. I have a new problem now and edited question with details. I did the correction you pointed to me too.

AndreKR Over a year ago

Please open a new question if you have another problem. (To which the solution has something to do with the word "into", I guess.)

newway Over a year ago

Im sory, i tried declare another variable do transfer value and return but its dont work, there is the ask with detail: stackoverflow.com/questions/8073404/…

Collectives™ on Stack Overflow

Error in mysql bigint variable declaration inside custom nextval function

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related