How to pass dynamic parameters to a MySQL view

Question

I have created the following view in MySQL:

CREATE VIEW MYVIEW AS (
SELECT A.FNAME
     , A.LNAME
     , B.EMAIL
FROM EMPLOYEE A, EMPEMAIL B
WHERE A.EID = :empId
  AND A.EID = B.EID
AND B.EMAILTYP = :emailType)

I have to make empId and emailType dynamic i.e. get the result for the desired values of empId and emailType at runtime.

What changes should be made in the code?

Thanks in advance.

Answer 1

You can use this solution with a function -

CREATE FUNCTION func() RETURNS int(11)
  RETURN @var;

CREATE VIEW view1 AS
  SELECT * FROM table1 WHERE id = func();

Using example:

SET @var = 1;
SELECT * FROM view1;

Answer 2

Just create the view without the parameters (i.e., to take care of the join only):

CREATE VIEW MYVIEW AS (
SELECT A.FNAME
     , A.LNAME
     , B.EMAIL
     , A.EID AS EID             -- added to be used in the WHERE
     , B.EMAILTYP AS EMAILTYP   -- added to be used in the WHERE
FROM EMPLOYEE A, EMPEMAIL B
WHERE A.EID = B.EID)

And apply the dynamic parameters when you query:

SELECT FNAME, LNAME, EMAIL
FROM   my_view
WHERE  eid = 'your_empId' AND emailtyp = 'your_emailType'

Note the WHERE shown above, it uses the two extra fields declared in the VIEW