mercredi 21 septembre 2016

use conditions (keyword 'IF' etc) in the formation of queries in stored procedures in mysql

I wrote a stored procedure (mysql, my first steps):

PROCEDURE `get_SiteAttendanceByPages`(IN _UserName VARCHAR(20) CHARSET utf8, IN _DateS BIGINT(20), IN _DateF BIGINT(20))
BEGIN

    IF _UserName = '' THEN
        SELECT 
            login_history.event_id, 
            COUNT(login_history.event_id) AS count 
        FROM 
            login_history 
        INNER JOIN 
            client_pages
        ON 
            login_history.event_id = client_pages.id
        WHERE 
            (login_history.time_stamp BETWEEN _DateS AND _DateF) AND client_pages.name LIKE '%[p]%'
        GROUP BY 
            login_history.event_id
        ORDER BY 
            client_pages.name;

    ELSE
        SELECT 
            login_history.event_id, 
            COUNT(login_history.event_id) AS count 
        FROM 
            login_history 
        INNER JOIN 
            client_pages
        ON 
            login_history.event_id = client_pages.id
        WHERE 
            login_history.user_name = _UserName AND (login_history.time_stamp BETWEEN _DateS AND _DateF) AND client_pages.name LIKE '%[p]%'
        GROUP BY 
            login_history.event_id
        ORDER BY 
            client_pages.name;

    END IF;

END

In fact, I would like to write shorter code. To do this, you need only make changes the conditions under which the sample is made from the table.

Is it possible to implement a mysql stored procedure? Those. do something like this:

        WHERE 
            (_UserName == '') ? ('') : ('login_history.user_name = _UserName AND') (login_history.time_stamp BETWEEN _DateS AND _DateF) AND client_pages.name LIKE '%[p]%'

?

Aucun commentaire:

Enregistrer un commentaire