0

I have a problem on MySQL stored procedure, already did another logic with IF THEN ELSE, but I still have problems which I cant understand...

The procedure consists on two user inputs: user_id and meet_id. The procedure define a variable called 'ue' which stores result of a bunch of validation (if user exists, if event exists, if event date is still valid, etc.).

After that, it does INSERT and UPDATE some data on multiple tables in IF THEN ELSE selector, and SELECT 1 (or 0) AS result depending of validation.

But my problem is: it always return me 0 as 'result', as if my validation variable was 0 when I do INSERT... And there is where things get weird, if I remove the INSERT [...]; line of the code, it returns me the value of validation correctly (1 or 0).

The code of the procedure is this one:

CREATE DEFINER=`nwouserf`@`localhost` 
PROCEDURE `join_event`(IN `user_id` BIGINT(64), IN `event_id` INT) 
NOT DETERMINISTIC MODIFIES SQL DATA SQL SECURITY DEFINER 

begin 
DECLARE ue INT; 
SET ue = EXISTS(SELECT 1 FROM users WHERE fb_uid=user_id) 
         AND EXISTS(SELECT 1 FROM meetup WHERE meet_id=event_id) 
         AND EXISTS(SELECT 1 FROM meetup WHERE date > NOW() AND meet_id = event_id) 
         AND EXISTS(SELECT 1 FROM meetup WHERE meet_id = event_id AND participants <= max_participants) 
         AND NOT EXISTS(SELECT 1 FROM meetup_participation WHERE fb_uid = user_id AND meet_id = event_id); 

IF ue = 1 THEN 

    INSERT INTO meetup_participation (fb_uid, meet_id) VALUES (user_id, event_id); 
    UPDATE users SET events_participated = events_participated + 1 WHERE fb_uid=user_id; 
    UPDATE meetup SET participants = participants + 1 WHERE meet_id=event_id; 
    SELECT 1 AS result; 

ELSEIF ue = 0 THEN 
    SELECT 0 AS result; 
ELSE 
    SELECT null AS result; 
END IF; 
end

Thanks in advance! I have been stuck on this for a while now, and can not figure out why.

Blank
  • 12,308
  • 1
  • 14
  • 32
Danton Heuer
  • 397
  • 4
  • 11

1 Answers1

1

You should define OUT parameter. Add

", OUT result INT"

immediately after the last IN parameter.

Valentin
  • 168
  • 7
  • Still not the right solution I think, as it works without OUT parameter and INSERT parameter... But I did a work-around with your answer as I did on the other procedures I have created... Had to change SELECT to SET, and do a double query. The only problem it is on PHP, it is not a good idea to multi-query in my opinion, as I have to CALL procedure and select output variable, it may fail on a query and continue to the next one. – Danton Heuer Feb 24 '17 at 01:55
  • And why you dont't create this as a function then? It looks much better from what I am hearing from you. – Valentin Feb 24 '17 at 01:59
  • I am new to MySQL, I might be wrong, but from what I have read so far, functions should not be used when you do modify data on tables (INSERT, UPDATE are not allowed). [link](http://stackoverflow.com/a/1179778/2612722) – Danton Heuer Feb 24 '17 at 02:05
  • Both are routines. Where did you read that, sounds wrong to me? Take a look at this link to read more about the [difference between procedure and function](http://stackoverflow.com/questions/2680745/differences-between-procedure-and-function-and-routine) as well. – Valentin Feb 24 '17 at 02:12
  • You are right, the link I gave on the end is SQL Server, not MySQL... Reading on MySQL documentation I wasn't able to find anything related, so I tested it and it worked out, thanks! – Danton Heuer Feb 24 '17 at 03:17