luffy luffy - 7 months ago 14
SQL Question

MYSQL: Create procedure ERRORS with syntax on Insert

So I'm trying to calculate total cost before insert into a database using a procedure. The entire idea is check if late payment is null then not null and if the value is 0.

So far I have this:

DELIMITER;;
CREATE OR REPLACE PROCEDURE add_transaction
(IN accTransactionId INT(11), IN auctionId INT(11), IN chittyAccNo INT(11), IN `date` DATETIME, IN amount DOUBLE, IN description VARCHAR(50),
IN transref VARCHAR(50), IN tranStat TINYINT, IN clearDate DATETIME, IN methodPaid DATETIME)
BEGIN
DECLARE latePayment int(11);
DECLARE total AS DOUBLE;

SELECT `LatePaymentFee`
INTO `latePayment`
FROM chittyusers
WHERE ChittyAccNo = NEW.ChittyAccNo;

CASE
WHEN latePayment IS NULL
THEN
SET total = amount ;

WHEN latePayment IS NOT NULL
THEN
SET total = amount + latePayment;
ELSE
SET total = amount;
END;
END CASE;

INSERT INTO `chittytransactions` (`ChittyTransactionID`, `AuctionID`, `ChittyAccNo`, `Date`, `Amount`,
`Description`, `TransRefence`, `TransStatus`, `ClearanceDate`, `PaymentMethod`)
VALUES (accTransactionId, auctionId, chittyAccNo, `date`, amount, description, transref, tranStat, clearDate, methodPaid)

END;;
DELIMiTER;


When I try to create this procedure I'm getting sql syntax error:


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


Am I doing something wrong and what can be changed. just Started using these so I don't fully understand how they work yet.

Also If you need the database this is it:

CREATE TABLE `chittytransactions` (
`ChittyTransactionID` int(11) NOT NULL,
`AuctionID` int(11) NOT NULL,
`ChittyAccNo` int(11) DEFAULT NULL,
`Date` datetime DEFAULT NULL,
`Amount` double DEFAULT NULL,
`Description` varchar(50) DEFAULT NULL,
`TransRefence` varchar(50) DEFAULT NULL COMMENT 'Reference from actual Bank transaction',
`TransStatus` tinyint(1) DEFAULT NULL COMMENT 'If Transaction Pending or Cleared',
`ClearanceDate` datetime DEFAULT NULL,
`PaymentMethod` int(1) DEFAULT NULL COMMENT '0- Cash, 1- bank transfer, 2- personal credit etc'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `chittyusers` (
`ChittyAccNo` int(11) NOT NULL,
`UserId` int(11) NOT NULL,
`ChittyID` int(11) NOT NULL,
`LatePaymentFee` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


Thank You Any help would be great

Answer
  • a delimiter is needed
  • "create or replace" doesn't seem to be accepted
  • there is an additional "as" in the total variable declaration
  • there is an additional "end" before "end case"
  • there is a missing semicolon at the end of the last insert

This seems to be accepted:

drop procedure if exists add_transaction;
delimiter $$
CREATE PROCEDURE add_transaction
(IN accTransactionId INT(11), IN auctionId INT(11), IN chittyAccNo INT(11), IN `date` DATETIME, IN amount DOUBLE, IN description VARCHAR(50),
IN transref VARCHAR(50), IN tranStat TINYINT, IN clearDate DATETIME, IN methodPaid DATETIME)
BEGIN
    DECLARE latePayment int(11);
    DECLARE total DOUBLE;

    SELECT `LatePaymentFee`
    INTO `latePayment`
    FROM chittyusers
    WHERE ChittyAccNo = NEW.ChittyAccNo;

    CASE 
        WHEN latePayment IS NULL 
    THEN
        SET total = amount ;

        WHEN latePayment IS NOT NULL
    THEN
        SET total = amount + latePayment;
    ELSE
        SET total = amount;
    END CASE;

    INSERT INTO `chittytransactions` (`ChittyTransactionID`, `AuctionID`, `ChittyAccNo`, `Date`, `Amount`,
     `Description`, `TransRefence`, `TransStatus`, `ClearanceDate`, `PaymentMethod`)
    VALUES (accTransactionId, auctionId, chittyAccNo, `date`, amount, description, transref, tranStat, clearDate, methodPaid);

END;
$$
Comments