Declare variable in MySQL stored procedure

Question

Declare variable in MySQL stored procedure

Navigation

#1 by (2 votes)

1

I have a question about how to perform the following procedure:

DELIMITER $$
create procedure insertaEmpleado (nombre varchar(50), apPat varchar(50), apMat varchar(50), apodo varchar(50), calle varchar(50), colonia varchar(50), numero varchar(5), municipio varchar(50), email varchar(50), sexo char(1), fechaNac date, telefono varchar(15), usuario varchar(50), pass varchar(50))
BEGIN
insert into persona values (null, nombre, apPat, apMat, apodo, calle, colonia, numero, municipio, email, sexo, fechaNac, telefono);
declare idPer;
set idPer = select max(idPer) from empleado;
insert into empleado values (null, usuario, pass, idPer);
END
$$

What I want is to enter values in two tables. But to enter the second table I need the [last id that was entered, but it marks me error. I'm not so good in MySQL. Someone to guide me in the correct syntax?

mysql

asked by José Licea de Ortiz 18.12.2016 в 05:31

source

1 answer

Avoid double click of an ajax, angular Access query with DateTimePicker type dates in C #

score 2 · Accepted Answer

First, to declare a variable as idPer , the name must start with the% symbol% co_%: @ .

Second, since it is a variable to which you want to assign the result of a query, you can do this directly in the query by assigning it with the operator @idPer :

select @idPer := max(idPer) from empleado;

So the following stored procedure should work correctly:

DELIMITER $$
create procedure insertaEmpleado (nombre varchar(50), apPat varchar(50), apMat varchar(50), apodo varchar(50), calle varchar(50), colonia varchar(50), numero varchar(5), municipio varchar(50), email varchar(50), sexo char(1), fechaNac date, telefono varchar(15), usuario varchar(50), pass varchar(50))
BEGIN
insert into persona values (null, nombre, apPat, apMat, apodo, calle, colonia, numero, municipio, email, sexo, fechaNac, telefono);
select @idPer := max(idPer) from empleado;
insert into empleado values (null, usuario, pass, @idPer);
END
$$
DELIMITER ;

Useful reference: User-Defined Variables .

However, even if it works, this way of doing it is not efficient. As the table := grows, the query that looks for the empleado will go more and more slowly.

Since it seems that you are using max(idPer) fields for your primary keys, the most efficient thing is that you use the function AUTO_INCREMENT to return the last LAST_INSERT_ID() inserted:

DELIMITER $$
create procedure insertaEmpleado (nombre varchar(50), apPat varchar(50), apMat varchar(50), apodo varchar(50), calle varchar(50), colonia varchar(50), numero varchar(5), municipio varchar(50), email varchar(50), sexo char(1), fechaNac date, telefono varchar(15), usuario varchar(50), pass varchar(50))
BEGIN
insert into persona values (null, nombre, apPat, apMat, apodo, calle, colonia, numero, municipio, email, sexo, fechaNac, telefono);
insert into empleado values (null, usuario, pass, LAST_INSERT_ID());
END
$$
DELIMITER ;

As a final comment, I recommend you always name the columns explicitly in your idPer statements. This will protect you from any changes in the structure of the tables, such as new columns, etc.