Pass parameter in View

3

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, p.email FROM conta c INNER JOIN pessoa p ON p.id = c.pessoa_id WHERE c.login = @parametro 
    
asked by anonymous 21.07.2015 / 21:31

2 answers

5

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, p.email 
    FROM conta c INNER JOIN pessoa p ON p.id = 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

    
22.07.2015 / 00:49
0

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:

    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 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: link

    
31.07.2017 / 16:06