1

I am writing stored function first time, I have written below code, I googled if I made synthetic problem, but did not find any solution, can you please, help me

CREATE FUNCTION GetOrderTotalAmountTesting(order_id int)
 RETURNS decimal(10,2)
 BEGIN
 declare order_amount_total decimal(10,2);
 SELECT ((sum(calc_unit_price)*quantity)+sum(calc_tax)+sum(shipping_cost)) INTO order_amount_total from order_details det where det.order_id =order_id;
  RETURN order_amount_total;
 END

Below are the errors Error SQL query:

CREATE FUNCTION GetOrderTotalAmountTestingassdasd(order_id int)
 RETURNS decimal(10,2)

 BEGIN
 declare order_amount_total decimal(10,2)

MySQL said: Documentation

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 5

F-M-A
  • 43
  • 8
  • 2
    Did you use a [`delimiter`](https://stackoverflow.com/q/10259504)? – Solarflare May 11 '19 at 10:39
  • No, where should I use – F-M-A May 11 '19 at 10:40
  • 1
    Check the linked answer, you need to specify a delimiter before your function (and afterwards to reset it). If you need one will depend on how you execute your code (you do not need one e.g. with phpmyadmin, but you need one with the workbench or the mysql client). – Solarflare May 11 '19 at 10:45
  • I am using phpmyadmin – F-M-A May 11 '19 at 10:46
  • 1
    Also, you should not use a variable name that equals a column name. `group by order_id` will group by your parameter, not that column. – Solarflare May 11 '19 at 10:46
  • 1
    So your function exists and is executable, but doesn't return the right value? E.g. can you run `select GetOrderTotalAmountTesting(1);` or does this error happen when you try to define your procedure? – Solarflare May 11 '19 at 10:48
  • Thank you, If I remove group by order_id the same errors are there – F-M-A May 11 '19 at 10:48
  • This erros comes when I define the function – F-M-A May 11 '19 at 10:49
  • 2
    Ok, for phpmyadmin, you need to either use the tab "routines" to define your function, or in the sql tab, you need to change the "delimiter"-value below the input field to something different than `;` (e.g. `$$`) – Solarflare May 11 '19 at 10:53
  • 2
    See [How to write a stored procedure in phpMyAdmin?](https://stackoverflow.com/q/17481890) for a detailed description of the 2 possibilites (the first answer is the sql tab, it looks different now, but you can see the "delimiter"-field below the input box, you need to change this value too). – Solarflare May 11 '19 at 11:02

0 Answers0