Can I create view with parameter in MySQL

Question

I have a view like this   CREATE VIEW MyView AS    SELECT Column FROM Table WHERE Value   2    I d like to make it more generic  it means to change 2  into a variable  I tried this   CREATE VIEW MyView AS    SELECT Column FROM Table WHERE Value    MyVariable    But MySQL doesn t allow this   I found an ugly workaround   CREATE FUNCTION GetMyVariable   RETURNS INTEGER DETERMINISTIC NO SQL BEGIN RETURN  MyVariable  END    And then the view is   CREATE VIEW MyView AS    SELECT Column FROM Table WHERE Value   GetMyVariable      But it looks really crappy  and the usage is also crappy - I have to set the  MyVariable before each usage of the view   Is there a solution  that I could use like this   SELECT Column FROM MyView 2  WHERE         The concrete situation is as follows  I have a table storing information about the denied request   CREATE TABLE Denial       Id INTEGER UNSIGNED AUTO INCREMENT          PRIMARY KEY Id       DateTime DATETIME NOT NULL      FeatureId MEDIUMINT UNSIGNED NOT NULL          FOREIGN KEY  FeatureId              REFERENCES Feature  Id              ON UPDATE CASCADE ON DELETE RESTRICT      UserHostId MEDIUMINT UNSIGNED NOT NULL          FOREIGN KEY  UserHostId              REFERENCES UserHost  Id              ON UPDATE CASCADE ON DELETE RESTRICT      Multiplicity MEDIUMINT UNSIGNED NOT NULL DEFAULT 1      UNIQUE INDEX DenialIndex  FeatureId  DateTime  UserHostId    ENGINE   InnoDB    A multiplicity is a number of identical requests recorded in the same second  I want to display a list of denials  but sometimes  when the application gets denied  it retries a couple times just to make sure  So usually  when the same user gets denial 3 times on the same feature in a couple seconds it is actually one denial  If we d have one more resource  to fulfill this request  the next two denials would not happen  So we want to group the denials in report allowing the user to specify the timespan in which denials should be grouped  E g  if we have denials  for user 1 on feature 1  in timestamps  1 2 24 26 27 45 and user wants to group denials that are closer to each other than 4 sec  he should get something like this  1  x2   24  x3   45  x1   We can assume  that spaces between real denials are much bigger than between duplications  I solved the problem in the following way   CREATE FUNCTION GetDenialMergingTime       RETURNS INTEGER UNSIGNED     DETERMINISTIC NO SQL BEGIN     IF ISNULL  DenialMergingTime  THEN         RETURN 0      ELSE         RETURN  DenialMergingTime      END IF  END   CREATE VIEW MergedDenialsViewHelper AS     SELECT MIN Second DateTime  AS GroupTime          First FeatureId          First UserHostId          SUM Second Multiplicity  AS MultiplicitySum     FROM Denial AS First          JOIN Denial AS Second              ON First FeatureId   Second FeatureId                 AND First UserHostId   Second UserHostId                 AND First DateTime  gt   Second DateTime                 AND First DateTime - Second DateTime  lt  GetDenialMergingTime       GROUP BY First DateTime  First FeatureId  First UserHostId  First Licenses   CREATE VIEW MergedDenials AS     SELECT GroupTime           FeatureId          UserHostId           MAX MultiplicitySum  AS MultiplicitySum     FROM MergedDenialsViewHelper     GROUP BY GroupTime  FeatureId  UserHostId    Then to show denials from user 1 and 2 on features 3 and 4 merged every 5 seconds all you have to do is   SET  DenialMergingTime    5  SELECT GroupTime  FeatureId  UserHostId  MultiplicitySum FROM MergedDenials WHERE UserHostId IN  1  2  AND FeatureId IN  3  4     I use the view because in it it s easy to filter data and to use it explicitly in the jQuery grid  automatically order  limit number of records and so on    But it s just an ugly workaround  Is there a proper way to do this

User · Answer

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

User · Answer

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

User · Answer

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

[mysql] Can I create view with parameter in MySQL?

Examples related to mysql

Examples related to stored-procedures

Examples related to view

Examples related to parameters