0
CREATE PRODECURE prc_inv_amounts
@inv_num DECIMAL
BEGIN
UPDATE INVOICE SET
INV_SUBTOTAL = (SELECT SUM(LINE_TOTAL) FROM LINE WHERE INV_NUMBER = @inv_num),
INV_TAX = INV_SUBTOTAL * 0.8,
INV_TOTAL = INV_SUBTOTAL + INV_TAX
WHERE INV_NUMBER = @inv_num
END

Error:

ERROR 1064 (42000) at line 1: 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 'PRODECURE prc_inv_amounts @inv_num DECIMAL BEGIN UPDATE INVOICE SET INV_SUBTOTAL' at line 1

Martin
  • 22,212
  • 11
  • 70
  • 132
drhorseman
  • 23
  • 6

1 Answers1

0

It looks like you have taken some sqlserver code and dumped it into mysql. Unfortunately sqlserver and mysql are quite different in SP syntax and variable declaration.

The code translated to mysql is

DELIMITER $$
CREATE PROcedURE prc_inv_amounts(inv_num int)
BEGIN
declare vinv_subtotal decimal(10,2);
SELECT SUM(LINE_TOTAL) into vinv_subtotal FROM LINE WHERE INV_NUMBER = inv_num;
UPDATE INVOICE SET
INV_SUBTOTAL = vinv_subtotal,
INV_TAX = vINV_SUBTOTAL * 0.8,
INV_TOTAL = vINV_SUBTOTAL + (vINV_SUBTOTAL * 0.8)
WHERE INV_NUMBER = inv_num;
END $$
delimiter ;

Note the use of delimiters https://dev.mysql.com/doc/refman/8.0/en/stored-programs-defining.html , the input parameters must be defined in brackets as part of the create statement https://dev.mysql.com/doc/refman/8.0/en/create-procedure.html, user defined an declared variables are not the same How to declare a variable in MySQL?, every statement must be terminated with a ; (semi colon)

I have also moved the sub total calculation.

P.Salmon
  • 17,104
  • 2
  • 12
  • 19