Monday, July 9, 2007

Finding max id using stored procedure (MYSQL)

drop procedure if exists maxid;
delimiter $$
create procedure maxid(IN tablename varchar(100),OUT mx int)
begin
declare exitWithMessage bool;
declare max_id,tmp int ;
declare cur cursor for select id from tv_user;
declare continue handler for not found
set exitWithMessage :=true;
set max_id=0;
set exitWithMessage :=false;
open cur;
LOOP1:loop
fetch cur into tmp;

if exitWithMessage then
set mx:=max_id;
leave LOOP1;
else
if tmp > max_id then
set max_id:=tmp;
end if;


end if;
end loop LOOP1;
end$$

call maxid('tv_user',@x);

No comments: