CHIVO SQL PAYMENT

DROP TABLE IF EXISTS `payment`;
CREATE TABLE `payment` (
    `id` int NOT NULL AUTO_INCREMENT,
    `txnId` varchar(20) NOT NULL,
    `paymentAmount` decimal(7,2) NOT NULL,
    `paymentCurrency` varchar(15) NOT NULL,
    `paymentStatus` varchar(25) NOT NULL,
    `userId` INT NOT NULL,
    `itemId` INT NOT NULL,
    `itemName` varchar(50) NOT NULL,
    `createdTime` datetime NOT NULL,
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

DROP PROCEDURE IF EXISTS pa_payment_register;
DELIMITER //
CREATE PROCEDURE pa_payment_register
(
    IN _txtnId VARCHAR(20),
    IN _paymentAmount DECIMAL(7,2),
    IN _paymentCurrency VARCHAR(15),
    IN _paymentStatus VARCHAR(25),
    IN _userId INT,
    IN _itemdId INT,
    IN _itemName VARCHAR(50),
    OUT rpta INT
)
BEGIN
    IF (SELECT COUNT(*) AS existsPayment FROM payment WHERE txnid = _txtnId) > 0 THEN
        SET rpta = 0;
    END IF;

    INSERT INTO payment (txtnId, paymentAmount, paymentCurrency, paymentStatus, userId, itemId, itemName, createdTime)
    VALUES (_txtnId, _paymentAmount, _paymentCurrency, _paymentStatus, _userId, _itemdId, _itemName, NOW());

    SET rpta = LAST_INSERT_ID();
END//
DELIMITER ;