Hell0 Hell0 - 3 months ago 7
MySQL Question

MySql function tax (calculate IVA)

The problem is i'm writing the function directly from shell(bash)
trying to translate the syntax from some IDE that i don't know !!
The original function is:

CREATE FUNCTION CalcIVA(@Price money)RETURNS money
AS
BEGIN
DECLARE @IVA money
SET @IVA = @Price * 1.16
RETURN @IVA
END;


So changed some syntax to work with
mysql>
prompt:

CREATE FUNCTION CalculoIVA(x INT)RETURNS INT DETERMINISTIC
BEGIN
DECLARE IVA INT
SET IVA = x *1.16
RETURN IVA
END;


What i'm doing wrong?


ERROR 1064 (42000): 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 'SET
IVA
=
x
*1.16 RETURN
IVA
END' at line 1


Answer

Determine what datatypes you want. INT won't work so hot with your 1.16 idea. You lose to rounding to an INT. The below does not use INT

DROP FUNCTION IF EXISTS CalculoIVA;
DELIMITER $$
CREATE FUNCTION CalculoIVA
(x DECIMAL(12,4))
RETURNS DECIMAL(12,4) 
DETERMINISTIC 
BEGIN 
  DECLARE IVA DECIMAL(12,4); -- OR FLOAT etc
  SET IVA = x *1.16; 
  RETURN IVA;
END;$$
DELIMITER ;

select CalculoIVA(9.12);
-- 10.5792

If you want the datatypes to be DECIMAL(12,2) or FLOAT just make the necessary changes.

Note: the DELIMITER wrapper is unnecessary for PHPMyAdmin users.