I am trying to discern when to use MySQL views, stored procs and functions.
For ex., I have a query I call from different code but the parameters are always the same:
SELECT *, IF(YEAR(date_add) = $as_of_this_year,DATE_FORMAT(date_aed, ‘%m-%d-%Y’),‘’) AS date_add,
IF(YEAR(date_term) = $as_of_this_year,DATE_FORMAT(date_term, ‘%m-%d-%Y’),‘’) AS date_term
FROM property
WHERE code =$codeID
AND (
date_terminated = ‘0000-00-00’
OR YEAR( date_term ) = $this_year
OR YEAR( date_term ) > $this_year
)
AND (
date_added = ‘0000-00-00’
OR YEAR( date_add ) = $this_year
OR YEAR( date_add ) < $this_year
)
In the above example, I would like to have that code reside in my db where I can call it like (using PHP PDO):
call thisProc(736, 2013)
I wrote on localhost sample:
CREATE DEFINER = root@localhost PROCEDURE pReport ( IN codeID INT, OUT address_1 INT ) NOT DETERMINISTIC READS SQL DATA SQL SECURITY DEFINER SELECT * , IF( YEAR( date_add ) =2013, DATE_FORMAT( date_add, ‘%m-%d-%Y’ ) , ‘’ ) AS date_add, IF( YEAR( date_term ) =2013, DATE_FORMAT( date_term, ‘%m-%d-%Y’ ) , ‘’ ) AS date_term
FROM property
WHERE code = codeID
AND (
date_term = ‘0000-00-00’
OR YEAR( date_term ) =2013
OR YEAR( date_term ) >2013
)
AND (
date_add = ‘0000-00-00’
OR YEAR( date_add ) =2013
OR YEAR( date_add ) <2013
)
If I run “call pReport(645,2013)” from SQL, it runs fine and returns data rows like a query. That’s what I want, even in an array. How do I get the data back and is this the best TOOL (VIEW, FUNCTION, STORED PROC) to get it? I realize that in the above proc., there is no defined OUT. Would I have to OUT for every field I want? If so, would I be better off using a different tool/method?
Many thanks.