MySQL: Making stored procedure multiple parameters optional

Question

I want to create a stored procedure with multiple input optional parameters. If parameters for at least one of the fields are given, I still want some result meeting the criteria.

Here is my table definition:

CREATE TABLE `tblinquiries` (
  `UID` varchar(50) DEFAULT NULL,
  `ReviewDate` date NOT NULL,
  `InquiryId` varchar(50) NOT NULL,
  `AuditStatus` varchar(50) DEFAULT NULL,
  `AssignedTo` varchar(50) DEFAULT NULL,
  `Result` int(2) DEFAULT NULL,
  `ResultCategories` int(2) DEFAULT NULL,
  `AuditBy` varchar(50) DEFAULT NULL,
  `Remarks` varchar(200) DEFAULT NULL,
  `StartTime` datetime DEFAULT NULL,
  `EndTime` datetime DEFAULT NULL,
   PRIMARY KEY (InquiryId)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Here is a sample data set:

INSERT INTO `tblinquiries` (`UID`, `ReviewDate`, `InquiryId`, `AuditStatus`, `AssignedTo`, `Result`, `ResultCategories`, `AuditBy`, `Remarks`, `StartTime`, `EndTime`) VALUES
('user1', '2018-07-07', '109933881', 'Assigned', 'auditor1', NULL, NULL, NULL, NULL, '2018-07-15 08:03:47', NULL),
('user2', '2018-07-07', '109933885', 'Assigned', 'auditor2', NULL, NULL, NULL, NULL, '2018-07-15 08:04:13', NULL),
('user3', '2018-07-07', '109933909', 'Complete', 'auditor3', 1, auditor3, NULL, NULL, '2018-07-15 08:06:42', '2018-07-15 08:10:42'),
('user4', '2018-07-08', '109933916', 'Check', NULL, NULL, NULL, NULL, NULL, NULL, NULL),
('user5', '2018-07-08', '109933917', 'Check', NULL, NULL, NULL, NULL, NULL, NULL, NULL),
('user6', '2018-07-08', '8790804', 'Complete', 'auditor1', 1, NULL, 'auditor1', NULL, '2018-07-14 21:19:25', '2018-07-14 21:21:29');

Here is my stored procedure:

DROP procedure IF EXISTS `spInquiries`;

DELIMITER $$
CREATE PROCEDURE `spInquiries` (IN `pReviewStartDate` DATE, IN `pReviewEndDate` DATE, IN `pAuditStartDate` DATE, IN `pAuditEndDate` DATE)
BEGIN
SELECT * FROM tblinquiries
  WHERE
  (ReviewDate BETWEEN pReviewStartDate AND pReviewEndDate)
  AND (DATE(EndTime) BETWEEN pAuditStartDate AND pAuditEndDate);
END$$

DELIMITER ;

After researching a lot, I found this stackoverflow question that has some similarity. But I don't how I can make 2 parameters (e.g - pAuditStartDate, pAuditEndDate) null for the WHERE clause.

I have added an SQL fiddle here.

I want some result for both the command:

CALL spInquiries('', '', '2018-07-01', '2018-07-31');
CALL spInquiries('2018-07-01', '2018-07-31', '', '');

How can I achieve that?

Answer 1

MySQL casts automatically the date type from strings so you can use string instead of date parameters. BTW the empty date ('') is not valid as MySQL date value.

After that you can change the value, if the comparison is from lower date then use '1900-01-01' as default and now() for higher range.

DELIMITER $$
drop procedure if exists `spInquiries`$$
CREATE PROCEDURE `spInquiries` (IN `pReviewStartDate` varchar(30), IN `pReviewEndDate` varchar(30),
                              IN `pAuditStartDate` varchar(30), IN `pAuditEndDate` varchar(30))
BEGIN

  -- sanitize the date ranges
  if pReviewStartDate = '' then 
    set pReviewStartDate = '1900-01-01';
  end if;
  if pAuditStartDate = '' then 
    set pAuditStartDate = '1900-01-01';
  end if;

  if pReviewEndDate = '' then 
    set pReviewEndDate = now();
  end if;

  if pAuditEndDate = '' then 
    set pAuditEndDate = now();
  end if;

  -- now you can run the query without problems

  SELECT * FROM tblinquiries
   WHERE
    (ReviewDate BETWEEN pReviewStartDate AND pReviewEndDate)
     AND (DATE(EndTime) BETWEEN pAuditStartDate AND pAuditEndDate);
END$$

Results:

mysql> CALL spInquiries('', '', '2018-07-01', '2018-07-31');
+-------+------------+-----------+-------------+------------+--------+------------------+----------+---------+---------------------+---------------------+
| UID   | ReviewDate | InquiryId | AuditStatus | AssignedTo | Result | ResultCategories | AuditBy  | Remarks | StartTime           | EndTime             |
+-------+------------+-----------+-------------+------------+--------+------------------+----------+---------+---------------------+---------------------+
| user3 | 2018-07-07 | 109933909 | Complete    | auditor3   |      1 |                0 | NULL     | NULL    | 2018-07-15 08:06:42 | 2018-07-15 08:10:42 |
| user6 | 2018-07-08 | 8790804   | Complete    | auditor1   |      1 |             NULL | auditor1 | NULL    | 2018-07-14 21:19:25 | 2018-07-14 21:21:29 |
+-------+------------+-----------+-------------+------------+--------+------------------+----------+---------+---------------------+---------------------+


mysql> CALL spInquiries('2018-07-01', '2018-07-31', '', '');
+-------+------------+-----------+-------------+------------+--------+------------------+----------+---------+---------------------+---------------------+
| UID   | ReviewDate | InquiryId | AuditStatus | AssignedTo | Result | ResultCategories | AuditBy  | Remarks | StartTime           | EndTime             |
+-------+------------+-----------+-------------+------------+--------+------------------+----------+---------+---------------------+---------------------+
| user3 | 2018-07-07 | 109933909 | Complete    | auditor3   |      1 |                0 | NULL     | NULL    | 2018-07-15 08:06:42 | 2018-07-15 08:10:42 |
| user6 | 2018-07-08 | 8790804   | Complete    | auditor1   |      1 |             NULL | auditor1 | NULL    | 2018-07-14 21:19:25 | 2018-07-14 21:21:29 |
+-------+------------+-----------+-------------+------------+--------+------------------+----------+---------+---------------------+---------------------+

Update:

I change the stored procedure to meet the new paramter

DELIMITER $$
drop procedure if exists `spInquiries`$$
CREATE PROCEDURE `spInquiries` (IN `pReviewStartDate` varchar(30), IN `pReviewEndDate` varchar(30),
                                IN `pAuditStartDate` varchar(30), IN `pAuditEndDate` varchar(30), 
                                pInquiryId VARCHAR(30))
BEGIN

  if pReviewStartDate = '' then 
    set @pReviewStartDate = '1900-01-01';
  else
    set @pReviewStartDate = pReviewStartDate;
  end if;
  if pAuditStartDate = '' then 
    set @pAuditStartDate = '1900-01-01';
  else
    set @pAuditStartDate = pAuditStartDate;
  end if;

  if pReviewEndDate = '' then 
    set @pReviewEndDate = now();
  else
    set @pReviewEndDate = pReviewEndDate;
  end if;

  if pAuditEndDate = '' then 
    set @pAuditEndDate = now();
  else
    set @pAuditEndDate = pAuditEndDate;
  end if;

  set @sql = 'SELECT * FROM tblinquiries WHERE 
            (ReviewDate BETWEEN ? AND ?)
             AND (DATE(EndTime) BETWEEN ? AND ?) ';

  if pInquiryId = '' then
    set @sql = concat(@sql,"AND InquiryId IS NULL");
  else
    set @sql = concat(@sql,"AND InquiryId =  '", pInquiryId,"'");
  end if; 

  PREPARE myquery FROM @sql;
  EXECUTE myquery using @pReviewStartDate,@pReviewEndDate,@pAuditStartDate,@pAuditEndDate;

END$$

DELIMITER ;