MYSQL ignore where clause if variable is null
<p>I have a stored procedure which is taking in 6 variables which may or may not be null depending on the filters applied on the front end of my web project. However, currently this stored procedure is returning nothing even if the all the filters are set to null. I am using MySQL workbench if that makes a difference. Previously I had been using separate stored procedures depending on the filters applied but this was getting too hard to manage as I added filters.</p>

<pre><code>CREATE DEFINER=`root`@`localhost` PROCEDURE `spFiltersGet`(IN jAlertId int, IN timeFrom timestamp, IN timeTo timestamp, IN seconds int, IN alertStatus varchar(255), IN jHostName varchar(255))
BEGIN 
DECLARE timeFromVal TIMESTAMP;
DECLARE timeToVal TIMESTAMP;
DECLARE fetchVal INT;
DECLARE offsetVal INT;
DECLARE alertIdVal INT;
DECLARE secondsVal INT;
DECLARE statusVal varchar(255);
DECLARE hostVal varchar(255);

SET timeFromVal = timeFrom;
SET timeToVal = timeTo;
SET fetchVal = 10;
SET offsetVal = 0;
SET alertIdVal = jAlertId;
SET secondsVal = seconds;
SET statusVal = alertStatus;
SET hostVal = jHostName;

SELECT * FROM job_alert
WHERE
(alertIdVal IS NULL OR alert_id = jAlertId)
    AND (timeFromVal IS NULL &amp;&amp; timeToVal IS NULL
    OR start_time BETWEEN timeFromVal AND timeToVal)
    AND (secondsVal IS NULL
    OR time_diff &gt;= seconds)
    AND (statusVal IS NULL
    OR status = alertStatus)
    AND (hostVal IS NULL OR host_name = jHostName)
ORDER BY alert_id DESC
LIMIT OFFSETVAL , FETCHVAL; 
END
</code></pre>

<p>If no filters are applied i.e all are set to null I am expecting this kind of output after I have converted it to JSON:</p>

<pre><code>{
  error: "null",
  jobStatus: "SUCCESS",
  startTime: "Sep 9, 2016 10:42:54 AM",
  endTime: "Sep 9, 2016 10:42:55 AM",
  timeDiff: 1217,
  runCount: 2,
  jobInstanceId: 834,
  host: "IT2561",
  nextRun: "Sep 9, 2016 10:52:54 AM",
  jobCount: 11,
  name: "Gift Certificate Delivery",
  id: 6,
  description: "Gift Certificate Delivery"
}
</code></pre>

<p>I had written this from looking at other StackOverflow answers, so I'm assuming that there is just something that I have missed out. I do not get any errors in my console, it just returns no records. </p>