mysql stored procedure: flow control

Question

i'm practicing with this stored procedure on a MYSQL database to make payments between accounts. it takes inputs of two accounts(acct1, acct2), an amount(amt) to be paid and outputs a confirmation message(pmessage)

it's supposed to make payment only when: (IF (balance1 >=amt) THEN ...), but somehow when i input any amount greater than the available balance, it still goes ahead to make payment. i don't understand. pls help.

here's the code:

DELIMITER //
CREATE PROCEDURE `make_payment`(IN `acct1` int(4), IN `acct2` int(4), IN `amt` float(10,2) unsigned, OUT `pmessage` varchar(100))

BEGIN
    DECLARE balance1 FLOAT;
    DECLARE balance2 FLOAT;

    IF(acct1 !=acct2) THEN 
        SELECT balance INTO balance1 FROM mydb.accounts
            WHERE account_no =acct1
            ORDER BY balance DESC LIMIT 1;

        SELECT balance INTO balance2 FROM mydb.accounts
            WHERE account_no =acct2
            ORDER BY balance DESC LIMIT 1;

        IF (balance1 >=amt) THEN 
            begin
                SET balance1 =balance1-amt;
                SET balance2 =balance2+amt;

                INSERT INTO mydb.accounts(account_no, outflow, balance)
                    VALUES(acct1, amt, balance1);

                INSERT INTO mydb.accounts(account_no, inflow, balance)
                    VALUES(acct2, amt, balance2);

                SET pmessage ="payment was successful";
            END;

        ELSE SET pmessage ="insufficient balance";
        END IF;
    END IF;
END //
DELIMITER ;

Can you give us some examples of values for acct1, acct2, amt, and the ACCOUNTS.BALANCE values for those accounts (i.e. balance1 and balance2) for which the above code doesn't work as expected? — Bob Jarvis - Слава Україні, May 15 '12 at 10:57
Whilst I doubt this is your problem in this case, you really ought to use fixed-point datatypes like `DECIMAL` for discrete quantities such as money, otherwise you may encounter undesirable rounding bugs due to the approximate nature of floating-point data types. Are you sure that the "available balance" is the *greatest* balance for the given account in the `accounts` table? I suspect you meant to `ORDER BY` date... you should also perform these operations within a transaction to prevent concurrency issues. — eggyal, May 15 '12 at 11:02
thanks@BobJarvis, @eggyal: you got it right, i meant to order by datetime, not balance. code works fine now, :D thanks very much. — okey_on, May 15 '12 at 12:58

score 1 · Accepted Answer · answered May 15 '12 at 13:30

(Upgrading to an answer)

You're ordering the accounts table by balance in descending order, therefore you're assuming that the "available balance" is the greatest balance for the given account in that table. You want instead to ORDER BY date and select the most recent record.

Please also note the other points made in my comment above, including:

You ought to be using fixed-point datatypes (such as DECIMAL) for currency, instead of floating-point types; and
You ought to perform these actions in a transaction to prevent concurrency issues.

mysql stored procedure: flow control

1 Answers1