Can I create view with parameter in MySQL?

Solution 1:

Actually if you create func:

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

and view:

create view h_parm as
select * from sw_hardware_big where unit_id = p1() ;

Then you can call a view with a parameter:

select s.* from (select @p1:=12 p) parm , h_parm s;

I hope it helps.

Solution 2:

CREATE VIEW MyView AS
   SELECT Column, Value FROM Table;


SELECT Column FROM MyView WHERE Value = 1;

Is the proper solution in MySQL, some other SQLs let you define Views more exactly.

Note: Unless the View is very complicated, MySQL will optimize this just fine.

Solution 3:

I previously came up with a different workaround that doesn't use stored procedures, but instead uses a parameter table and some connection_id() magic.

EDIT (Copied up from comments)

create a table that contains a column called connection_id (make it a bigint). Place columns in that table for parameters for the view. Put a primary key on the connection_id. replace into the parameter table and use CONNECTION_ID() to populate the connection_id value. In the view use a cross join to the parameter table and put WHERE param_table.connection_id = CONNECTION_ID(). This will cross join with only one row from the parameter table which is what you want. You can then use the other columns in the where clause for example where orders.order_id = param_table.order_id.