CODEWITHSUNDEEP
mysqlsqlstored-procedures
Nour Mawla
Nour Mawla

Reputation: 351

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

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)

Upvotes: 1

Views: 2306

Answers (2)

Afdzal Kamal
Afdzal Kamal

Reputation: 91

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:

  1. Call TMF646_GetAppointmentsRaw('' , '' , '', '')
  2. 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.

Upvotes: 1

ahmed
ahmed

Reputation: 9191

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.

Upvotes: 2

Related Questions