1

I am creating a procedure that returns a total amount of sales between two dates, which the user puts in. It's sloppy code, I think, but I cannot get it to run and I've already changed it many times. I'm worried that I am moving futher from the answer rather than closer. Here is my code:

DELIMITER //
CREATE PROCEDURE salesBetween (IN date1(date), date2(date),
                               OUT totalsale(decimal(10,2)))
BEGIN
   SELECT SUM(hslineitem.numOrdered * hslineitem.price)
   FROM hslineitem, hsorders
   WHERE hsorders.orderId = hslineitem.orderId
   AND (hsorders.orderDate BETWEEN date1 AND date2)
   AND orderdate = date1, date2;
END //
DELIMITER ;

THis is the first time I've tried to use more than one piece of user input, so I am guessing my issue has to do with that. Most everything I can find online is not mysql specific, so it hasn't been much help.

Madhur Bhaiya
  • 28,155
  • 10
  • 49
  • 57
Shawn Benson
  • 137
  • 1
  • 7
  • 1
    `AND orderdate = date1, date2` is outright wrong... What is the purpose behind this condition ? – Madhur Bhaiya Nov 24 '18 at 20:23
  • Can I cut it completely? It was my understanding that stored procedure names needed to link to something in a table. Since I am using two input values, what should I use to link it? – Shawn Benson Nov 24 '18 at 20:25
  • I am still getting the following error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(date), IN date2(date), OUT totalsale(decimal(10' at line 1 – Shawn Benson Nov 24 '18 at 20:34

1 Answers1

2

Please don't use Old comma based Implicit joins and use Modern Explicit Join based syntax

Also, I do not see the purpose of AND orderdate = date1, date2; and it is syntactically wrong as well. I have removed it.

DELIMITER //
CREATE PROCEDURE salesBetween (IN date1 date, /* no brackets needed around datatype */ 
                               IN date2 date, /* specify IN for this parameter */
                               OUT totalsale decimal(10,2))
BEGIN
   SELECT SUM(hslineitem.numOrdered * hslineitem.price) INTO totalsale 
   FROM hslineitem  /* Changed to JOIN .. ON syntax */
   JOIN hsorders ON hsorders.orderId = hslineitem.orderId
   WHERE (hsorders.orderDate BETWEEN date1 AND date2);
END //
DELIMITER ;
Madhur Bhaiya
  • 28,155
  • 10
  • 49
  • 57
  • 1
    @CReilly in that case, you can remove `AND orderdate = date1, date2`; also add `IN` in front of `date2` in the parameter declaration. – Madhur Bhaiya Nov 24 '18 at 20:28
  • Oh, I just now noticed the update, my apologies. Is there a reason the date datatype does not require parentheses where others do? – Shawn Benson Nov 24 '18 at 20:35
  • 1
    @CReilly none require parentheses around them. Not even around decimal. parentheses are for further parameters to decimal, not around it. – Madhur Bhaiya Nov 24 '18 at 20:36
  • The `totalsale` OUT-variable is not used in the procedure. – slaakso Nov 25 '18 at 08:35
  • @slaakso thanks for spotting. added `into ..` it was not there in the original question also; so I think it was mostly about getting syntax errors out first. – Madhur Bhaiya Nov 25 '18 at 08:36