Declare output variable. MySql stored procedure

Question

Declare output variable. MySql stored procedure

Navigation

#1 by (1 votes)
#2 by (1 votes)

3

DELIMITER $$
CREATE PROCEDURE 'capturar_consecutivo'(numero int) RETURNS int
BEGIN
    DECLARE num int DEFAULT 0;
    SET num = ( SELECT * FROM consecutivo where consecutivo.numero=numero );
    RETURN num
END
$$
DELIMITER ;

I'm trying to create this stored procedure with return values, but when I create it the system throws me an error:

"# 1064 - Something is wrong in its syntax near 'int BEGIN"

But I do not understand what the error is, according to me the syntax is correct, someone to guide me in the correct syntax?

mysql mysqli

asked by Fredy Muñoz 30.08.2018 в 16:35

source

2 answers

1

RETURN is used for functions, not for Store Procedure.

Remove RETURNS int and RETURN at the end and you're done:

DELIMITER $$
CREATE PROCEDURE 'capturar_consecutivo'(IN numero int)
BEGIN
    DECLARE num int DEFAULT 0;
    SET num = ( SELECT * FROM consecutivo where consecutivo.numero=numero );
END
$$
DELIMITER ;

answered by 30.08.2018 в 16:53

Internet Explorer Compatibility with HTML Error: ENOENT: no such file or directory, stat 'C: \ public \ index.html'

score 1 · Accepted Answer

If you want to return a value you have to use a function and not a stored procedure

To create the function, you would have to, for example, like this:

DELIMITER $$
CREATE FUNCTION 'capturar_consecutivo'(numero   INT) 
RETURNS INT
BEGIN
    DECLARE num INT DEFAULT 0;
    SET num = ( SELECT * FROM consecutivo where consecutivo.numero=numero );
    RETURN num;
END
$$ DELIMITER ;

But also take into account that the part:

SET num = ( SELECT * FROM consecutivo where consecutivo.numero=numero );

Make sure you only return a single field and a single record, otherwise you will have an error.