Description:
----[For better reports, please attach the log file after submitting. You can find it in C:\Users\qwerty\AppData\Roaming\MySQL\Workbench\log\wb.log]
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.
How to repeat:
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
Suggested fix:
No suggestions.
Description: ----[For better reports, please attach the log file after submitting. You can find it in C:\Users\qwerty\AppData\Roaming\MySQL\Workbench\log\wb.log] 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. How to repeat: 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 Suggested fix: No suggestions.