SQLize Online / PHPize Online  /  SQLtest Online

create table table_1(name varchar(20) NOT NULL UNIQUE); insert into table_1(name) values('3d'),('4th'),('1st'); create table table_2(id int AUTO_INCREMENT,name varchar(20),primary key(id)); insert into table_2(name)values ('1st'),('2nd'),('3d'); create table test_result_table(str varchar(1000)); drop procedure if exists custom_procedure; delimiter $$; create procedure custom_procedure() begin declare str VARCHAR(1000) default ''; declare cat_id int default 0; declare c_name varchar(20); declare done int default false; declare product_cursor cursor for select name from table_1; declare continue handler for not found set done = true; open product_cursor; while done do fetch product_cursor into c_name; select distinct id into cat_id from table_2 where name = c_name; SET str = CONCAT(str,' ', c_name, ' ', cat_id,'\n'); END while; close product_cursor; insert into test_result_table values(str); select * from test_result_table; end $$; delimiter ; call custom_procedure();