drop procedure if exists routhlp;
delimiter go
create procedure routhlp( db varchar(64), rnam varchar(64) )
begin
  select routine_name into rnam
  from information_schema.routines
  where routine_schema=db and routine_name like concat('%', rnam, '%' ) limit 1;
  if found_rows() > 0 then
    begin
      select routine_schema, routine_type, specific_name into @db, @typ, @nam
      from information_schema.routines
      where routine_schema=db and routine_name=rnam;
      set @params =
        ( select group_concat( 
            trim( concat( if(parameter_mode='IN','', parameter_mode), ' ', 
                          parameter_name, ' ', 
                          data_type,
                          ifnull(character_maximum_length, '')
                        ) 
                 )
            order by ordinal_position
          )
          from information_schema.parameters
          where specific_schema=@db and specific_name=@nam 
        );
      select @db as db, @typ as type, @nam as routine, @params as params;
    end;
  else
    select 'Routine not found' as result;
  end if;
end;
go
delimiter ;

Last updated 19 Dec 2024