0

I created a procedure on mysql. Here's my sql.

drop procedure if exists proc_serial_no;
DELIMITER //
CREATE PROCEDURE `proc_serial_no`(IN comp_code varchar(20), IN meat_rule_one varchar(20),OUT serial_no int)

LANGUAGE SQL 
NOT DETERMINISTIC 
MODIFIES SQL DATA 
SQL SECURITY  INVOKER 
COMMENT 'serial no generator for trace code' 

begin

declare current_no int default 0;
select serial_no into current_no from t_serial_no where comp_code = comp_code and rule = meat_rule_one;
if current_no = 0 
then insert into t_serial_no (id,no,comp_code,rule) values ( replace(uuid(),'-',''),1,comp_code,meat_rule_one); 
else update t_serial_no set no = no + 1 where comp_code = comp_code and rule = meat_rule_one;
end if;
select serial_no = current_no + 1;
end

I'm expecting the out param [serial_no] to increase every time I call the procedure,but it's always zero.

When I change sql related to the out param to

select no into  serial_no from t_serial_no where comp_code = comp_code and rule = meat_rule_one;

It worked! I could't figure out why. Anyone can answer this would be in great help!

Synge
  • 1
  • 1

1 Answers1

0

This may be right or wrong. But it may be due to ,

  1. Everytime you call the procedure , the local variable current_no is by its default value it is 0.

  2. When the scope moves out of the procedure and again when you call that procedure, it again starts with its default value 0 again and again.

And also,

  1. May be everytime you pass the value 0 to serial_no param everytime you call that procedure and it again starts with 0 everytime when the scope moves out and comes in.
Anands23
  • 758
  • 9
  • 19