Stored Procedure MySql

Posted on 2007-10-08
Last Modified: 2013-11-25
I have a table called 'capital' in mysql.  I am subtracting 10 from a field called 'pendingCapitalAvailable'.  To accomplish this, I am considering using a called procedure that I will call from Matlab.  I need to make sure the table is locked until I finish the procedure.

Below is the code I wrote to create the procedure, it does not work and tells me to look at the manual to figure it out.  Can you tell me what is wrong with it?

create procedure lockedUpdateOfCapital(IN capitalUsed INT)


UPDATE capital SET pendingCapitalAvailable = pendingCapitalAvailable - capitalUsed;
UPDATE capital SET marginAvailable = marginAvailable - capitalUsed;


I assume to execute it I would type 'call lockedUpdateOfCapital(102)' in the query browser.  Where can I find a list of the stored procedures?

Server info:
MySQL 5.0.45-community-nt via TCP/IP
MySQL Client Version 5.1.11
InnoDB tables but I could use MYISAM tables if necessary
Question by:jmokrauer
    LVL 16

    Accepted Solution

    Where can I find a list of the stored procedures?
    LVL 142

    Assisted Solution

    by:Guy Hengel [angelIII / a3]
    just a side node:
    your procedure code does not seem to use any WHERE clause on the table???

    now, how do you try to compile the procedure? I ask, because I don't see the DELIMITER statement ...

    next, you could update the table in 1 update statement (assuming that either you have indeed no where clause, or the same where clause...)
    that would also avoid that you needed an explicit transaction.

    note that, you missed a ; after the commit...


    create procedure lockedUpdateOfCapital(IN capitalUsed INT)


    UPDATE `capital` SET pendingCapitalAvailable = pendingCapitalAvailable - capitalUsed
                     , marginAvailable = marginAvailable - capitalUsed;


     delimiter ;

    Featured Post

    Gigs: Get Your Project Delivered by an Expert

    Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

    Join & Write a Comment

    How to remove superseded packages in windows w60 or w61 installation media (.wim) or online system to prevent unnecessary space. w60 means Windows Vista or Windows Server 2008. w61 means Windows 7 or Windows Server 2008 R2. There are various …
    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
    The viewer will learn additional member functions of the vector class. Specifically, the capacity and swap member functions will be introduced.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now