MySQL error in a procedure #1351 - View's SELECT contains a variable or parameter
As you see - view cannot use variables, it is a limitation. You are trying to create a view with exact WHERE condition; construct the CREATE VIEW statement text firstly, then use prepared statements to execute the statement, it will help you. But, do you really need it, to create, create and create new view?
EDIT: This is the content of the reference in the comment.
Olexandr Melnyk A simple workaround for MySQL's limitation on local variables usage in views is to use a function, which returns variable's value:
create function book_subject
returns varchar(64) as
return @book_subject;
create view thematical_books as
select title
, author
from books
where subject = book_subject();
Roger Fernandez Guri
Software Engineer. Avid traveler. Founded Precious and Joiner. Worked at Icebergs, Netquest and Wakoopa. I love to create things that didn't exist yesterday.
Updated on February 05, 2020Comments
-
Roger Fernandez Guri over 4 years
PhpMyAdmin doesn't accept the parameters "id_jugador_IN" inside the procedure.
This is the procedure we're executing:
SET GLOBAL event_scheduler = 1; use traviandatabase; DELIMITER $$ DROP PROCEDURE IF EXISTS sp_monitoritzacio_jugador $$ CREATE PROCEDURE sp_monitoritzacio_jugador(IN id_jugador_IN INT(10)) BEGIN CREATE OR REPLACE VIEW dades_ususari AS SELECT j.nom AS jugador, j.sexe AS sexe, j.edat AS edat, j.correu AS correu, a.nom AS alianca, p.nom AS pais, c.nom_ciutat AS ciutat FROM jugador AS j JOIN alianca AS a JOIN pais AS p JOIN ciutat AS c ON j.id_jugador = id_jugador_IN GROUP BY id_jugador_IN; END $$ DELIMITER ; CALL sp_monitoritzacio_jugador(1);
And this is the table "jugador":
DROP TABLE IF EXISTS `jugador`; CREATE TABLE `jugador` ( `id_jugador` int(10) NOT NULL AUTO_INCREMENT, `id_raca` int(10) NOT NULL, `id_pais` int(10) NOT NULL, `id_alianca` int(10) DEFAULT '0', `nom` varchar(20) NOT NULL, `sexe` enum('Home','Dona') NOT NULL, `edat` int(10) NOT NULL, `correu` varchar(20) NOT NULL, PRIMARY KEY (`id_jugador`), KEY `jugador-alianca` (`id_alianca`), KEY `id_pais` (`id_pais`), KEY `id_raca` (`id_raca`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
So it doesn't stop displaying the same error:
MySQL said: 1351 - View's SELECT contains a variable or parameter