how to limit data from dates passed in mysql stored procedure

Question

The stored proc is set up so the user can call it using any dates. I want to be able to restrict the date passed to two months.

Right now when the user calls the report without any dates selected, the report server crashes because there is too much data. I want to be able to either limit the rows if dates passed is more than 2 months.

    DELIMITER ;;
    DROP PROCEDURE IF EXISTS rpt_missing_payroll_files_report_test;;
    CREATE PROCEDURE `rpt_missing_payroll_files_report_test`(
      IN BEGIN_DATE VARCHAR(255),
      IN END_DATE VARCHAR(255)
    )
    BEGIN

    SELECT c.id,
           c.name,
           ip.name AS icp_name,
           s.name AS country_name,
           sc.name AS pm_name,
           pc.frequency,
           pc.check_date AS check_date,
           pc.transmit_date,
           IFNULL(ps.updated_at, ph.updated_at) AS submit_date,
           cpp.created_at AS date_received,
           ifnull(cpp.import_filename,'N/A') AS import_filename,
           CASE WHEN cpp.created_at IS NOT NULL THEN 1 END AS date_received_sum,
           CASE WHEN cpp.success = 1 THEN 1 END AS file_successsum,
           DATEDIFF(pc.check_date,cpp.created_at) AS date_diff,
           DATE_FORMAT(BEGIN_DATE,'%m/%d/%Y') AS BeginDate,
           DATE_FORMAT(END_DATE,'%m/%d/%Y') AS EndDate
    FROM co_payroll_calendars pc
    inner join co_infos c on pc.co_info_id = c.id                                                                                                                               
    inner join icp_countries icp on c.icp_country_id = icp.id
    INNER JOIN sys_countries s ON icp.sys_country_id = s.id
    INNER JOIN icp_infos ip ON ip.id = icp.icp_info_id
    LEFT OUTER JOIN co_payroll_entry_setups ps ON pc.id = ps.co_payroll_calendar_id                                                   
    LEFT OUTER JOIN co_payroll_entry_setup_histories ph ON pc.id = ph.co_payroll_calendar_id                                
    LEFT OUTER JOIN co_payroll_processes cpp ON cpp.check_date >= (pc.check_date - INTERVAL 10 DAY ) AND cpp.co_info_id = c.id
    LEFT OUTER JOIN sys_csrs sc ON c.sys_csr_id = sc.id
    -- below is the part i probably need to change
    WHERE  (ifnull(BEGIN_DATE,'') = ''
           OR pc.check_date >= BEGIN_DATE)
      AND (ifnull(END_DATE,'') = ''
           OR pc.check_date <= END_DATE)
    GROUP BY id,
             check_date
    ORDER BY country_name, check_date DESC, c.id;

    END;;
    DELIMITER ;

The following should be allowed

CALL rpt_missing_payroll_files_report_test('2017-06-01','2017-07-01')

The following should not be allowed or limited to a certain amount of rows

CALL rpt_missing_payroll_files_report_test('2016-01-01','2017-07-01')

Answer 1

Set the parameters to the right type (ie, use a DATE, not VARCHAR).

You should also check this:

LEFT OUTER JOIN co_payroll_processes cpp 
ON cpp.check_date >= (pc.check_date - INTERVAL 10 DAY ) 
AND cpp.co_info_id = c.id

Notice the inequality will select all rows with matching id having dates posterior to (pc.check_date - INTERVAL 10 DAY ). This might be a lot, or not, depending on your schema. You should test the query without the GROUP BY and ORDER BY, and examine what it returns exactly. If you find lots of duplicates, then you could make the query a lot faster by using better JOIN conditions.

Now, about your main question, the answer is simple: add this to your proc

IF END_DATE > (BEGIN_DATE + INTERVAL 2 MONTH) THEN
    raise an error
ELSE
    your huge SELECT
END IF

It's quite simple. Here is how to raise an error in a mysql stored proc (the way to do it depends on your version).

You can also simply do "SELECT 'your message here'" and the message string will be returned to the client (which may then crash, since it expected a certain set of columns, so it would be better to raise an error).