Pass parameter in View

Question

How do I pass a parameter to a view in Mysql? Using SQL Server simply 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 1

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

There are some alternative forms to define the where of a view dynamically.

• Create a function

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

  Then create the view, referencing to function as a clause where and then call the function as if it were 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 working on SQL Fiddle

• Use a clause where

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

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

Adapted response of this post do Soen

Answer 2

It’s not necessarily true that we don’t pass parameters to a view. It is quite true that we can not define parameters (well, at least in my experiences did not happen). But who said we need?

Watch the view, with some joins:

    CREATE 
VIEW `view_ranking_candidato` AS
    SELECT 
        `candidato`.`nick` AS `nick`,
        `candidato`.`nomeCompleto` AS `nomeCompleto`,
        SUM(`desafios`.`pontuacao`) AS `totalPontos`
    FROM
        (((`acertoCandidato`
        JOIN `respostaSubmetida` ON ((`respostaSubmetida`.`idRespSubmetida` = `acertoCandidato`.`idRespostaSubmetida`)))
        JOIN `desafios` ON ((`desafios`.`idDesafio` = `acertoCandidato`.`idDesafio`)))
        JOIN `candidato` ON ((`candidato`.`idCandidato` = `respostaSubmetida`.`idCandidato`)))
    GROUP BY `candidato`.`idCandidato`

If you want to do a parameterized search, just include the Where clause in the select that will consume the view:

select * from view_ranking_candidato ---> vai executar a view sem parâmetros

select * from view_ranking_candidato Where nick='tester' ---> vai executar a view incluindo a cláusula where.

Adapted from: http://profvictorhugo.esy.es/bd/mysql/mysql-view-tutorial/