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

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)

CodePudding user response：

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.