How to create a Stored Procedure with Optional DateTime Parameters in mysql

Question

So the parameters in the stored procedure are optional, meaning they can be null. The issue with my cade is that there is no output whenever I call the SP. I need my params to be able to accept null as well

CREATE PROCEDURE `GET`(
in startDate DATETIME,
in endDate DATETIME
)
BEGIN
SELECT * FROM DB.table
WHERE
DB.table.colunm >= startDate
AND
DB.table.colunm  <= endDate;
END

when I call the stored procedure, I never get any result

call GET(2022-05-28, 2022-05-30)

call GET(null, null)

What does NULL in the parameter value means? "with no restrictions"? if so then use COALESCE() and replace NULL with the value which is maximal or minimal for the datatype. — Akina
– Akina, Commented Jun 1, 2022 at 9:05
@Akina what I mean by null is that I want to have the ability to not pass any date to the SP and have it return all the data. Like ignore the where and just select * — Nour Mawla
– Nour Mawla, Commented Jun 1, 2022 at 10:02
.. WHERE DB.table.column BETWEEN COALESCE(startDate, '1000-01-01 00:00:00') AND COALESCE(endDate, 9999-12-31 23:59:59);. PS. BEGIN-END may be removed, they're excess, and in this case you do not need to use DELIMITER additionally. — Akina
– Akina, Commented Jun 1, 2022 at 10:25

ahmed · Accepted Answer · 2022-06-01 10:22:07Z

2

If you want to retrieve all records when you pass the null value, you can use the IF... ELSE... Statement to determine what your Stored Procedure will select.

Check the following SP:

CREATE PROCEDURE GETDT(
in startDate DATETIME,
in endDate DATETIME
)
BEGIN
if startDate is null or endDate is null then
  SELECT * FROM test;
else
  SELECT * FROM test
  WHERE
  test.sdate >= startDate
  AND
  test.edate  <= endDate;
end if;
END

See the result from db-fiddle.

answered Jun 1, 2022 at 10:22

ahmed

9,2013 gold badges11 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Afdzal Kamal · Accepted Answer · 2024-01-15 05:54:24Z

If your query is complex and you don't want to create multiple select statement you can have the "IF" function inside the "WHERE" statement.

This SP expect 4 input, but in can be null.

example of calls:

Call TMF646_GetAppointmentsRaw('' , '' , '', '')
Call TMF646_GetAppointmentsRaw('id01', '' , '' ,'2024-01-14T19:41:55.000Z')

  

CREATE PROCEDURE `TMF646_GetAppointmentsRaw`(
   IN input_id VARCHAR(45),
   IN input_status VARCHAR(25),
   IN input_creationDate VARCHAR(50),
   IN input_lastUpdate VARCHAR(50)
)
BEGIN
   SELECT 
      a.id AS id, 
      status,
      DATE_FORMAT(created_at, '%Y-%m-%dT%H:%i:%s.000Z') AS creationDate,
      DATE_FORMAT(updated_at, '%Y-%m-%dT%H:%i:%s.000Z') AS lastUpdate
   FROM tmforum.appointments a
   WHERE 1=1
      AND IF(input_id != '', a.id, '') LIKE input_id
      AND IF(input_status != '', a.status, '') LIKE input_status
      AND IF(input_creationDate != '', a.created_at, '') = 
          IF(input_creationDate != '', (STR_TO_DATE(input_creationDate, '%Y-%m-%dT%H:%i:%s.000Z')), '')
      AND IF(input_lastUpdate != '', a.updated_at, '') = 
          IF(input_lastUpdate != '', (STR_TO_DATE(input_lastUpdate, '%Y-%m-%dT%H:%i:%s.000Z')), '');
END

If '' date is received it will check against '' value as well so the condition will be true.

But if there is value, it will check against created_at value in the db.

Collectives™ on Stack Overflow

How to create a Stored Procedure with Optional DateTime Parameters in mysql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related