how to do an update, delete and select using Stored Procedure?

Question

When I try to use an update, delete or select, the code executes the commands in all rows.

Update example

   DELIMITER $$
CREATE PROCEDURE update_cinema
(IN NOME_FANTASIA varchar(100), IN LOGRADOURO varchar(100),IN CIDADE varchar(100), IN ESTADO varchar(100),IN LOTACAO int, in ID_CINEMA int)
BEGIN
UPDATE tb_cinema
SET NOME_FANTASIA = nome_fantasia , LOGRADOURO=LOGRADOURO, CIDADE= cidade, ESTADO= estado, LOTACAO= lotacao
WHERE id_cinema = id_cinema;
END $$
DELIMITER ;

CALL UPDATE_CINEMA ('Muda', 'Por favor', 'da certo', 'MS', 400, 16)

The update executes in 16,17 and 18 instead of only in 16

Select example

    DELIMITER $$
CREATE PROCEDURE SELECT_cinema
(IN NOME_FANTASIA varchar(100))
BEGIN
SELECT * from tb_cinema
 where nome_fantasia = nome_fantasia;
END $$
DELIMITER ;

It is selecting all rows instead of the row that i choose

Delete example

DELIMITER $$
CREATE PROCEDURE DELETE_CINEMA
(IN NOME_FANTASIA varchar(100))
BEGIN
DELETE  from tb_cinema
 where nome_fantasia = nome_fantasia;
END $$
DELIMITER ;

call DELETE_cinema ('test_Select')

It is deleting all rows

Answer 1

You need give the parameter names that are different from the actual column names. Otherwise, it is ambiguous whether the name refer to the parameter or the column name. As a consequence, the where conditions become no-ops, just like the set assignments.

Here is an example for the update procedure. I prefixed all parameter names with p_ to remove the ambiguity:

create procedure update_cinema(
    in p_nome_fantasia varchar(100), 
    in p_logradouro varchar(100),
    in p_cidade varchar(100), 
    in p_estado varchar(100),
    in p_lotacao int, 
    in p_id_cinema int
)
begin
    update tb_cinema
    set 
        nome_fantasia = p_nome_fantasia, 
        logradouro = p_logradouro, 
        cidade = p_cidade, 
        estado = p_estado, 
        lotacao = p_lotacao
    where id_cinema = p_id_cinema;
end $$
delimiter ;