MySQL how create a view to work with query parameters

Question

Working with MySQL 8

I have the cuenta_contable table (it is Self-Referential) and the following View:

CREATE VIEW cuenta_contable_union_view_with_code_32 AS
(
SELECT
    cc.code, cc.description
FROM
    cuenta_contable cc
WHERE
    cc.code = '32'
)
UNION
(
SELECT
    cc1.code,
    cc1.description
FROM
    cuenta_contable cc1
INNER JOIN
    cuenta_contable cc2
ON
    cc1.parent_cuenta_contable = cc2.id_cuenta_contable
WHERE
    cc2.code = '32'
)
ORDER BY
    code ASC;

and works how is expected, it returns 8 rows.

Observe it uses:

cc.code = '32'
cc2.code = '32'

Thinking in have this view be more dynamic I need replace these two 32 with ?

cc.code = ?
cc2.code = ?

to do the following:

SELECT * FROM cuenta_contable_union_view_with_code WHERE cc.code='30' and cc2.code='30'
SELECT * FROM cuenta_contable_union_view_with_code WHERE cc.code='40' and cc2.code='40'

Sadly is not possible, if I use ? in the view declaration then happens:

SQL Error [1064] [42000]: You have an error in your SQL syntax; 
check the manual that corresponds to your MySQL server version 
for the right syntax to use near '?

I have read other posts, but working with Stored Procedures. Mostly about:

Can I create view with parameter in MySQL?

In this case is not an option use Stored Procedures because exists the option or risk in the future to change Database.

I tried with (the two WHERE were removed):

CREATE VIEW cuenta_contable_union_view_with_code AS
(
SELECT
    cc.code, cc.description
FROM
    cuenta_contable cc
)
UNION
(
SELECT
    cc1.code,
    cc1.description
FROM
    cuenta_contable cc1
INNER JOIN
    cuenta_contable cc2
ON
    cc1.parent_cuenta_contable = cc2.id_cuenta_contable
)
ORDER BY
    code ASC;

Therefore using:

SELECT * FROM cuenta_contable_union_view_with_code;

Works but return everything - more of 8

SELECT * FROM cuenta_contable_union_view_with_code WHERE code = '32';

Observe the WHERE part. It returns just 1 row and not 8 how the 'static' first version.

Therefore how solve this?

My worst scenario is create many 'static' views, but it is verbose and not very practical

user_205608 · Answer

As you know, SQL views cannot have parameters. But you can filter them with WHERE clauses and so forth.

This is the pattern of your task.

  CREATE VIEW something AS (
    SELECT item1, item2, item3, item4
      FROM ...
   );

Then you use that view like this

 SELECT item2, item3, item4 
   FROM something
  WHERE item1 = 'constant'

Don't worry too much about performance; the query planner is clever about optimizing queries from views. In your case it might look like this.

CREATE VIEW cuenta_contable_union AS (
    SELECT cc.code, cc.description, cc.code selector_code
      FROM cuenta_contable cc
     UNION
    SELECT cc1.code, cc1.description, cc2.code selector_code
      FROM cuenta_contable cc1
      JOIN cuenta_contable cc2 
             ON cc1.parent_cuenta_contable = cc2.id_cuenta_contable
 );

Then to use the view to choose the 32-value items you want, do this:

SELECT code, description
  FROM cuenta_contable_union
 WHERE selector_code = 32
 ORDER BY code ASC;

Next time it will be

SELECT code, description
  FROM cuenta_contable_union
 WHERE selector_code IN (10, 20, 30)
 ORDER BY code ASC;

or whatever your application requires.

If your table is large, this view might be faster if you use UNION ALL in place of UNION.

MySQL how create a view to work with query parameters

1 Answers