Pass parameter in View

Posted on

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. Here is the view of the problem in SQL:

CREATE VIEW 'view_conta_pessoa' AS SELECT p.nome, FROM conta c INNER JOIN pessoa p ON = 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 the where clause and then call the function as if it were a parameter:

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

    Finally, you can “call view with a parameter”

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

See working in SQL Fiddle

  • Use a where

    You can also put a where clause in the call of view :

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

Adapted response from this SOen post


It is not necessarily true that we do not pass parameters to a view. It is quite true that we can not set parameters (well, at least in my experiments did not roll). But who said we need it?

Look at the view, with some joins:

VIEW 'view_ranking_candidato' AS
        'candidato'.'nick' AS 'nick',
        'candidato'.'nomeCompleto' AS 'nomeCompleto',
        SUM('desafios'.'pontuacao') AS 'totalPontos'
        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 parametrized 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:


Leave a Reply

Your email address will not be published. Required fields are marked *