Stored procedure insert in MySQL

Question

Stored procedure insert in MySQL

Navigation

#1 by (0 votes)
#2 by (0 votes)
#3 by (0 votes)

0

Create a stored procedure that id auto incremente , I did it in SQLSERVER but I do not know how to do it in MySQL

CREATE PROCEDURE insersertar
(@nombemple varchar(30))
AS
BEGIN
DECLARE 
@num int;
DECLARE
 @id char(5);
SELECT @num = COUNT(*) FROM empleado;
SELECT @id='E' + right('000' + cast(@num + 1 as varchar), 4);
INSERT INTO empleado VALUES (@id, @nombemple);

mysql

asked by Paul Julio 06.05.2017 в 01:39

source

3 answers

BATCH: Count the folders inside a directory and return them in a variable Enter a default value to params

score 0 · Answer 1

Hi, I'm somewhat out of shape with mysql but try the following:

DELIMITER //
CREATE PROCEDURE insersertar
(IN nombemple VARCHAR(30))
BEGIN

    DECLARE NUM INT;
    DECLARE ID CHAR(5);

    SET NUM = COUNT(*) FROM EMPLEADO;
    SET ID = 'E' + right('000' + cast(@num + 1 as varchar), 4);
    INSERT INTO EMPLEADO VALUES (ID,EMPLEADO);  

END //
DELIMITER ;

I hope you serve

Greetings.

score 0 · Answer 2

There are many ways to do this, I will add one where I will use two variables defined as in your procedure in SQL , num will have the value of the rows that are currently in your table and id the value generado

To generate the new code we used the function LEFT () , which will return the number of characters to the izquierda specified in the second parameter.

SELECT LEFT('holamundo', 4); // retona 'hola'

With this in mind, the second parameter will be the amount total of characters accepted by the column in your table (5) , menos the number of characters obtained with the function CHAR_LENGTH (I'm not sure if it's the best option for get the amount of an int) of the value returned by the function count , all this will be concatenated with the value of num , finally the Insert of the values is realized.

CREATE PROCEDURE insertsertar(IN nombemple  varchar(80) )
BEGIN 
    DECLARE num int;
    DECLARE id  char(5);
    SET num = (SELECT COUNT(*)+1 FROM empleado); 
    SET id = CONCAT(LEFT('E0000', 5-CHAR_LENGTH(num)),num);
    INSERT INTO empleado values (id ,nombemple);
END IF;

score 0 · Answer 3

Hello in mysql we can use an alter table to support us to perform the auto incremental automatically.

First you must know the maximum number plus one

SELECT COUNT(*)+1 as resultado FROM empleado;

Then you perform the ateracion of the table assigning the resulting value

ALTER TABLE tbl AUTO_INCREMENT = (resultado);

That way the Primary Key will always increase automatically and you do not have to make an extra query