MySQL Forums :: Stored Procedures :: Condition was unable to be followed by MySQL.


Advanced Search

Condition was unable to be followed by MySQL.
Posted by: Rob Bert ()
Date: June 02, 2016 06:36PM

Please someone help me:
I'm a newbie here in mysql.

The level_id field of the 5th record should be inserted with a value of
1 if @total variable is less than equal to 5. First, user-defined
variable @total will hold the count value from select statement. an if
statement willl checked whether @total value is less than equal to 5.
I'm wondering why it is only started inserting if the @total value of
less than equal to 3. It should be less than equal to 5. if i switch the
value of 5, it only starts encoding of level_id field value of 1 at the
6th record. Of course, if the first condition was unable to be followed
correctly then the rest is also wrong. Can you kindly find out what is wrong
with my code.

================================================================================
Here is the code:

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_EncodePerson`(
in p_memberId varchar(45),
in p_encodeId varchar(100)
)
BEGIN
DECLARE Lev0 varchar(5);
DECLARE Lev1 varchar(5);
DECLARE Lev2 varchar(5);
DECLARE Lev3 varchar(5);
DECLARE Lev4 varchar(5);
DECLARE Lev5 varchar(5);
DECLARE Lev6 varchar(5);
DECLARE Lev7 varchar(5);
DECLARE Lev8 varchar(5);
DECLARE Lev9 varchar(5);
DECLARE Lev10 varchar(5);

SET Lev0 = "0";
SET Lev1 = "1";
SET Lev2 = "2";
SET Lev3 = "3";
SET Lev4 = "4";
SET Lev5 = "5";
SET Lev6 = "6";
SET Lev7 = "7";
SET Lev8 = "8";
SET Lev9 = "9";
SET Lev10 = "10";

SET @total = 0;

SELECT @total := count(*) from gph_acquiredpeople
where member_Id = p_memberId;

IF @total <= 3 THEN
INSERT INTO gph_acquiredpeople
(member_id,encoded_mem_Id,date_encoded,level_id)
VALUES (p_memberId,p_encodeId,NOW(),Lev0);
UPDATE gph_memberstatus SET available ="0", taken = "1"
WHERE Id_number = p_encodeId;

ELSEIF @total > 5 AND @total <= 25 THEN
INSERT INTO gph_acquiredpeople
(member_id,encoded_mem_Id,date_encoded,level_id)
VALUES (p_memberId,p_encodeId,NOW(),Lev1);
UPDATE gph_memberstatus SET available ="0", taken = "1"
WHERE Id_number = p_encodeId;

ELSEIF @total > 25 AND @total <= 125 THEN
INSERT INTO gph_acquiredpeople
(member_id,encoded_mem_Id,date_encoded,level_id)
VALUES (p_memberId,p_encodeId,NOW(),Lev2);
UPDATE gph_memberstatus SET available ="0", taken = "1"
WHERE Id_number = p_encodeId;

ELSEIF @total > 125 AND @total <= 625 THEN
INSERT INTO gph_acquiredpeople
(member_id,encoded_mem_Id,date_encoded,level_id)
VALUES (p_memberId,p_encodeId,NOW(),Lev3);
UPDATE gph_memberstatus SET available ="0", taken = "1"
WHERE Id_number = p_encodeId;

ELSEIF @total > 625 AND @total <= 3125 THEN
INSERT INTO gph_acquiredpeople
(member_id,encoded_mem_Id,date_encoded,level_id)
VALUES (p_memberId,p_encodeId,NOW(),Lev4);
UPDATE gph_memberstatus SET available ="0", taken = "1"
WHERE Id_number = p_encodeId;

ELSE
BEGIN
END;
END IF;
END
================================================================================
Thanks and more power...

Options: ReplyQuote


Subject Views Written By Posted
Condition was unable to be followed by MySQL. 3053 Rob Bert 06/02/2016 06:36PM
Re: Condition was unable to be followed by MySQL. 331 Peter Brawley 06/02/2016 07:43PM
Re: Condition was unable to be followed by MySQL. 352 Rob Bert 06/02/2016 08:11PM
Re: Condition was unable to be followed by MySQL. 346 Peter Brawley 06/03/2016 06:08PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.