CODEWITHSUNDEEP
mysqlsqlstored-procedures
rainbasa
rainbasa

Reputation: 69

Handling Dates as IN Parameters of a MySQL Stored Procedure

What I want to Achieve I am creating a Procedure on my MySQL Database which will get the Total of the Net Amount column labeled as netamt where the Purchase Date labeled as purdate is between StartDate and EndDate. I tried two separate ways with my SQL Query. When I ran the CREATE Query, MySQL would accept it and create my Procedure. But, when I try calling the Procedure it returns NULL.

My SQL Query

Here is how my first CREATE code looks like:

CREATE DEFINER=`*****`@`localhost` PROCEDURE `Payable_Total_Net`(
  IN startDate DATE,
  IN endDate DATE,
  OUT totalNet DECIMAL(19,4)
)
BEGIN
 SELECT
    SUM(COALESCE(p.netamt,0)) INTO totalNet
    FROM Payables p
    WHERE p.purdate  >= str_to_date(startDate, '%Y-%m-d%') AND p.purdate <= str_to_date(endDate, '%Y-%m-d%');
END

Here is how my second CREATE code looks like:

CREATE DEFINER=`*****`@`localhost` PROCEDURE `Payable_Total_Net`(
  IN startDate DATE,
  IN endDate DATE,
  OUT totalNet DECIMAL(19,4)
)
BEGIN
 SELECT
    SUM(COALESCE(p.netamt,0)) INTO totalNet
    FROM Payables p
    WHERE p.purdate  BETWEEN str_to_date(startDate, '%Y-%m-d%') AND  str_to_date(endDate, '%Y-%m-d%');
END

This is how it looks as a normal query with the right result:

SELECT
SUM(COALESCE(p.netamt,0)) as totalNet
FROM Payables p
WHERE p.purdate BETWEEN '2018-01-01' AND '2018-12-31'

This is how I call the Procedure:

Call Payable_Total_Net('2018-01-01','2018-12-31',@totalNet);
SELECT @totalNet;

My Currently Stored Procedure My Currently Stored Procedure

My Queries to call Stored Procedure My Queries to Call Procedure

The Result if I run both queries at once Result if Query All

The Result if I only run the Select query Result if I Query Select

This is my expected result using the normal query enter image description here

Upvotes: 1

Views: 1889

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270001

You are passing the values as dates. You don't need to convert them:

CREATE DEFINER=`*****`@`localhost` PROCEDURE `Payable_Total_Net`(
  IN in_startDate DATE,
  IN in_endDate DATE,
  OUT out_totalNet DECIMAL(19,4)
)
BEGIN
  SELECT COALESCE(SUM(p.netamt), 0) INTO out_totalNet
  FROM Payables p
  WHERE p.purdate >= in_startDate AND 
        p.purdate <= in_endDate;
END;

Upvotes: 1

Related Questions