Error # 1064 when creating a stored procedure

1

Good attempt to make a kardex and the process of movements I want to do with a stored procedure.

This is the Relational Model of the database:

Where product_cod of the kardex table is the product_id of the product table and the concept field is of type enum ('Input', 'Output') of the kardex table.

The procedure I need to do is that every time I enter the kardex table a product with its axis quantity field: 1 and in the concept option it is equal to 'Input' I update the product table with the amount entered. The same with the exit option.

Axis: if in the product table I have a product with the quantity 5 and in the kardex table I select the product and I give it the entry option and in the amount income 3 then the product table of that product the quantity would be 8 , and likewise in exit since in this it is subtracted.

I currently have this code:

DELIMITER $$
CREATE PROCEDURE ACTUALIZA_STOCK (n_cantidad int,  n_concepto enum, id int)
BEGIN
if n_concepto='Entrada' then
UPDATE producto set cantidad=cantidad+n_cantidad WHERE id_producto=id;
end if
if n_concepto='Salida' then
UPDATE producto set cantidad=cantidad-n_cantidad WHERE id_producto=id;
end if;
END $$ 

but I get the second error:

# 1064 - Something is wrong in its syntax near 'id int)    BEGIN    if n_concepto = 'Entrada' then    UPDATE product set quantity = cant 'on line 1

Your help would be appreciated.

    
asked by Juanzu 22.06.2017 в 21:49
source

2 answers

2

You have a major error, and that is that columns of type ENUM are not created as such, but with their values.

From the data type ENUM the MySQL documentation says the following:

  

A ENUM is a string object with a value selected from a   List of allowed values that are explicitly listed in the   Specification of column at the time of creation of the table .

For example, the syntax of a ENUM column to create, insert, select, is something like this:

CREATE TABLE shirts (
    name VARCHAR(40),
    size ENUM('x-small', 'small', 'medium', 'large', 'x-large')
);

INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'),
  ('polo shirt','small');

SELECT name, size FROM shirts WHERE size = 'medium';

Your declaration of n_concepto enum does not meet those criteria, that is why the error. Also, you are missing a semicolon at the end of an if, but that is a minor error that is corrected by putting the semicolon.

I tried this in rextester and it works, look at the column of type enum as it is created :

VIEW DEMO

DELIMITER $$
CREATE PROCEDURE ACTUALIZA_STOCK  
(n_cantidad int, n_concepto enum('Entrada', 'Salida'),  id int) 
BEGIN 
if n_concepto='Entrada' then
UPDATE producto set cantidad=cantidad+n_cantidad WHERE id_producto=id;
end if;
if n_concepto='Salida' then
UPDATE producto set cantidad=cantidad-n_cantidad WHERE id_producto=id;
end if;
END;$$ 
    
answered by 22.06.2017 / 22:32
source
1

The main problem sees @ ACedano's response

Another detail is that instead of using id , use for example id_prod , try not to use words reserved .

DELIMITER $$
CREATE PROCEDURE ACTUALIZA_STOCK (n_cantidad int,  n_concepto enum, id_prod int)
BEGIN
if n_concepto='Entrada' then
UPDATE producto set cantidad=cantidad+n_cantidad WHERE id_producto=id_prod;
end if
if n_concepto='Salida' then
UPDATE producto set cantidad=cantidad-n_cantidad WHERE id_producto=id_prod;
end if;
END $$ 
    
answered by 22.06.2017 в 22:10