StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POStored procedure in mysql strange behaviour
text
Body
copied!<p>This stored procedure is taking care of material reservations. The basic idea is that it checks how much of THIS material are already hired and if there are still materials in stock i will insert a new order. If there is already a reservation for this material for this reservation id i am updating the amount.</p> <p>Maybe im doing something wrong but when i try to add a new reservation it works. The update NEVER works and when there is already a reservation for an specif material id it's not possible to rent it with another reservation id. </p> <p>I give you an example: </p> <pre><code>CALL aantal_besch_mat_van_tot('2007-03-13','2007-03-14',15,6,50,'procedure test lol'); </code></pre> <p>Ok so this works but when i execute it again the amount 50 should go to 50 + previous so it should be 100 it never updates.</p> <p>Also when you would hire the same material with another reservation id it's not working. example:</p> <pre><code>CALL aantal_besch_mat_van_tot('2007-03-13','2007-03-14',15,7,50,'Im hiring this material'); </code></pre> <p>That is also not working.</p> <p>I already printed out al value's and there getting the right amount.</p> <p>Here you can find my stored procedure code:</p> <pre><code> DELIMITER $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `fabiola`.`aantal_besch_mat_van_tot`(IN `p_datum_van` date,IN `p_datum_tot` date,IN `p_mat_id` int, IN `p_res_id` int, IN `p_nodig` int, IN `p_mat_opmerking` VARCHAR(255)) BEGIN DECLARE aantal INT DEFAULT 0; DECLARE tot INT DEFAULT 0; DECLARE upda INT DEFAULT 0; DECLARE nieuwa INT DEFAULT 0; DECLARE voriga INT DEFAULT 0; DECLARE test INT DEFAULT 0; DECLARE res_van datetime; DECLARE res_tot datetime; -- Overeenkomstige data selecteren SELECT r.incheckdatum, r.uitcheckdatum FROM reservaties r WHERE r.id = p_res_id INTO res_van, res_tot; SELECT mpr.aantal FROM materialen_per_reservatie mpr WHERE mpr.materialen_id = p_mat_id AND ( (p_datum_van >= mpr.datum_van AND p_datum_tot <= mpr.datum_tot) -- overlap: binnen OR (p_datum_van <= mpr.datum_van AND p_datum_tot >= mpr.datum_van) -- overlap: voor+in OR (p_datum_van <= mpr.datum_tot AND p_datum_tot >= mpr.datum_tot) -- overlap: na+in OR (p_datum_van <= mpr.datum_van AND p_datum_tot >= mpr.datum_tot) -- overlap:omsluitend ) INTO aantal; -- The total amount of materials SELECT m.aantal_beschikbaar FROM materialen m WHERE m.id = p_mat_id INTO tot; -- The test variable is holding the amount of materials that's still available SELECT tot-aantal INTO test; -- Checking if im not ordering more then there is available IF p_nodig < test THEN -- Checking if this material is already hired from this reservation id SELECT mpra.id, mpra.aantal FROM materialen_per_reservatie mpra WHERE (mpra.reservaties_id = p_res_id AND mpra.materialen_id = p_mat_id) INTO upda, voriga; -- if voriga is bigger then zero it means that there is already an reservatie for this material for this reservation so im not inserting but updating IF voriga > 0 THEN -- Selecting the previous amount and add it with the p_nodig amount SELECT voriga+p_nodig INTO nieuwa; UPDATE materialen_per_reservatie SET materialen_per_reservatie.aantal = nieuwa WHERE reservaties_id = p_res_id AND materialen_id = p_mat_id; ELSE -- There is no reservation for this material with this reservation id so i my insert a new row INSERT INTO materialen_per_reservatie(reservaties_id, materialen_id, aantal, effectief_gebruikt, opmerking, datum_van, datum_tot) VALUES (p_res_id, p_mat_id, p_nodig, p_nodig, p_mat_opmerking, p_datum_van, p_datum_tot); END IF; END IF; END$$ </code></pre> <p>I tested out both INSERT / UPDATE query's separate and they are working.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload