Re: how to retreive the parameters of the stored procedures fro information_schema
Hi!
The information_schema will provide support for that in the future. meanwhile, you can get your hands on the info if you are willing to grant select on the proc table in the mysql database.
The parameters are stored as a single string in the param_list column of the proc table in the mysql database. So, you still need to parse out the individual parameters if you need them. This is not too hard, as they are separated by a comma.
Here's an example function that returns the ddl for a routine. It does not show how to parse the parameters but if you can't hack it post back and I'll see if i can help.
drop function if exists ddl_sp
//
create function ddl_sp(
p_schema varchar(64)
, p_name varchar(64)
)
returns longtext
sql security definer
not deterministic
comment 'Returns the source code for the specified function or procedure.'
begin
declare v_routine_ddl longtext;
select concat(
'CREATE ',p.type
, ' `',p.db,'`.`',p.name,'`'
, '(',convert(p.param_list using utf8),')','\n'
, if( p.type='FUNCTION'
, concat('RETURNS ',p.returns,'\n')
, ''
)
, 'LANGUAGE ',p.language,'\n'
, if( p.is_deterministic='NO'
, 'NOT '
, ''
), 'DETERMINISTIC','\n'
, replace(p.sql_data_access,'_',' '),'\n'
, 'SQL SECURITY ',p.security_type,'\n'
, convert(p.body using utf8)
)
into v_routine_ddl
from mysql.proc p
where p.db = p_schema
and p.name = p_name
;
return v_routine_ddl
;
end;
//