MySQL Stored Procedure with parameter dynamic filtering and sorting

I recently worked on a web application where we had to retrieve user specific dashboard data in real-time with dynamic paging, dynamic column sorting (ASC or DESC), and dynamic data filtering (i.e by year). For the normal use case, there would not be too many user specific data, but for a small sub-set of users they would have a large amount of user specific data. Also, out count parameters were needed in addition to the select column data output.  For this reason, MySQL stored procedures were chosen to achieve this real-time requirement.


Environment: 
MySQL 5.7.10.0 on Windows 10
MySQL 5.7.10.0 on CentOS 6.7
MySQL 5.7.10.0 on RHEL 7

A MySQL temp table was chosen (over a view) because:

1. Needed to pass parameter dynamic data for filtering and sorting in the SELECT statement to retrieve user data. With a Temp table that is possible vs a View's select statement cannot contain a variable or a parameter (it's a known limitation).
• NOTE: If a View's select statement could contain variables and parameters it would have been chosen because:
• Data in a view is always current because it is dynamically generated, whereas the data in a temp table reflects the state of the database at the time it was populated and is only created once per session.
• We want to always update the View real-time, even in the same session for a user.
2. Temp tables are created per session, so you can have the "same name" temp table across different sessions. MySQL will maintain different "copies" of it.

The "skeleton" of the stored procedure strategy is below:

DROP PROCEDURE IF EXISTS retrieve_user_specific_data;
DELIMITER $$
CREATE PROCEDURE retrieve_user_specific_data(IN p_user_id varchar(100), IN p_results_per_page int, IN p_pagination_offset int, 
                IN p_sort_column varchar(100), IN p_sort_dir varchar(5),
                IN p_fy_list varchar(100),
                OUT p_total_count int, 
                OUT p_total_count_received int)

BEGIN

    -- Log parameters
    -- SELECT p_user_id, p_results_per_page, p_pagination_offset, p_sort_column, p_sort_dir, p_fy_list;
    
    DECLARE v_user_id INT(11);
    DECLARE v_rvw_id_list VARCHAR(3000);
    DECLARE v_total_count INT(11);
    DECLARE v_total_count_received INT(11); 
    
    -- Allow p_results_per_page, p_pagination_offset, p_sort_column, and p_sort_dir to be optional    
    -- integers have to be null or a value
    SET p_results_per_page = IFNULL(p_results_per_page, 9999);
    SET p_pagination_offset = IFNULL(p_pagination_offset, 0);
    -- strings can be null, empty string '', or a value
    SET p_sort_column = IF (NULLIF(p_sort_column, ' ') IS NULL, 'days_open', p_sort_column);
    SET p_sort_dir = IF (NULLIF(p_sort_dir, ' ') IS NULL, 'DESC', p_sort_dir);
     
    -- get user id from p_user_id by querying user table 
    SET v_user_id = (<SELECT to get data>);
    
    -- Log user_id
    -- SELECT v_user_id;
    
    -- get review id list of reviews associated to user
    SET v_rvw_id_list = (<SELECT to get data>);
    
    -- create temp table with all data relevant to user
    DROP TEMPORARY TABLE IF EXISTS temp_user_specific_data;
    CREATE TEMPORARY TABLE temp_user_specific_data
    AS
        (<SELECT to get data>);
        
    -- get total count with filters from temp temp_user_specific_data data table
    -- create temp_user_specific_data_total_counts to hold total count info
    DROP TEMPORARY TABLE IF EXISTS temp_user_specific_data_total_counts;
    CREATE TEMPORARY TABLE temp_user_specific_data_total_counts
    AS
        (SELECT 
             column1, column2, count(*) AS total_count
        FROM 
        temp_user_specific_data
        
        -- if a filter is null, then don't apply it
        -- if a filter is NOT null then apply it    
        WHERE  
            (NULLIF(p_fy_list, ' ') IS NULL OR FIND_IN_SET(fiscal_year, p_fy_list))
                    
            -- NOTE:  Do not need ORDER BY for total counts 
            GROUP BY column1
    );
        
    -- Retrieve total counts into OUT parameters.  If the count is null, set it to 0  
    -- Total
    SET v_total_count = (<SELECT temp_user_specific_data_total_counts to get data>);
    SET p_total_count = IF (v_total_count IS NULL, 0, v_total_count);
    
    -- Response Received
    SET v_total_count_received = (<SELECT temp_user_specific_data_total_counts to get data>);
    SET p_total_count_received = IF (v_total_count_received IS NULL, 0, v_total_count_received);
    
    -- Log total counts
    -- SELECT * from temp_user_specific_data_total_counts;
    
    DROP TEMPORARY TABLE IF EXISTS temp_user_specific_data_total_counts; 
    
    -- select rows from temp_user_specific_data temp data table with the filters, sort and pagination passed to this function 
    -- get paged data set
    SELECT 
        *
    FROM 
    temp_user_specific_data
    
    -- if a filter is null, then don't apply it
    -- if a filter is NOT null then apply it    
    WHERE  
        (NULLIF(p_fy_list, ' ') IS NULL OR FIND_IN_SET(fiscal_year, p_fy_list))
        
    ORDER BY   
        -- Answer found - https://stackoverflow.com/questions/15085149/passing-dynamic-order-by-in-stored-procedure
        -- Need a case for each sort direction and each data type
        
        -- days_open order
        CASE 
             WHEN p_sort_dir <> 'ASC' THEN 0
             WHEN p_sort_column = 'days_open' THEN days_open_count
        END ASC
        
        -- other column sorts and directions removed
   
    LIMIT p_pagination_offset,p_results_per_page
    ;
    
    DROP TEMPORARY TABLE IF EXISTS temp_user_specific_data;        
    
END$$
DELIMITER ;