mysql – Pass parameter in View

Question:

How do I pass a parameter to a view in MySQL? Using SQL Server, just put WHERE u.nome = @nome that would solve the problem. Below is the view of the problem in SQL:

CREATE VIEW `view_conta_pessoa` AS SELECT p.nome, p.email FROM conta c INNER JOIN pessoa p ON p.id = c.pessoa_id WHERE c.login = @parametro 

Answer:

There is no way to pass parameters to a view in MySQL . More details can be seen in the official documentation.

There are some alternative ways to set the where of a view dynamically.

• Create a function

   create function funcao() returns INTEGER DETERMINISTIC NO SQL return @funcao;

  Then create the view , referencing the function as a where clause and then call the function as a parameter:

   CREATE VIEW `view_conta_pessoa` AS SELECT p.nome, p.email FROM conta c INNER JOIN pessoa p ON p.id = c.pessoa_id WHERE c.coluna = funcao();

  And finally, it is possible to "call the view with a parameter"

   select p.* from (select @funcao:=12 f) s, view_conta_pessoa p;

See it working in SQL Fiddle

• use a where clause

  It is also possible to put a where clause in the view call :

   SELECT * FROM view_conta_pessoa c WHERE c.login = 'login'

Adapted response from this post from SOen