CODEWITHSUNDEEP
mysqlstored-procedures
icc97
icc97

Reputation: 12863

How to list mysql stored procedure parameters

What is the SQL to list the parameters of a MySQL stored procdure? The information_schema.routines table holds the stored procedure names but there doesn't seem to be a standard place where the parameters are stored.

Upvotes: 6

Views: 19550

Answers (3)

ravkry
ravkry

Reputation: 1

I wrote a procedure that returns a list of parameters in the table. It may be useful to someone.

CREATE PROCEDURE Micropsitta.'getParams'(parname varchar(255)) 
    BEGIN
    set @wyraz=(select cast(param_list as char)
    from mysql.proc 
    where type='PROCEDURE'  
    and name COLLATE utf8_polish_ci = parname COLLATE utf8_polish_ci);
    drop TEMPORARY table if exists tab;
    CREATE TEMPORARY TABLE tab (substrings varchar(255)) ENGINE=MEMORY;
    IF ((@wyraz) <> '')
     THEN
     set @firstchar=1;
     set @spacje=1;
     set @lenghtWyraz=(select CHAR_LENGTH(@wyraz));
     set @lenght=1;
     while @lenght < @lenghtWyraz
     DO
      set @lenght=(select CHAR_LENGTH(SUBSTRING_INDEX(@wyraz,' ',@spacje)));
      set @sub=(select SUBSTRING(@wyraz,@firstchar,@lenght-@firstchar+1));
      set @firstchar=(select CHAR_LENGTH(SUBSTRING_INDEX(@wyraz,',',@spacje)))+2;
      insert into tab (substrings)
      select @sub;
      set @spacje=@spacje+1;
     end while;
    END IF;
    select substrings as params from tab where substrings <>'';
    END;

Upvotes: 0

dash
dash

Reputation: 91540

More recent versions of MySQL (5.5.3 and above) introduced the information_schema.parameters object which should give you the information you need;

SELECT * 
FROM information_schema.parameters 
WHERE SPECIFIC_NAME = 'your_procedure';

Earlier versions of MySql rely on having access to the mysql.proc table; the column 'param_list' has all of the parameter information in there for the procedure with the name you are interested in. The information is decidedly non-normalised, though, as it is stored as comma separated string:

SELECT param_list FROM mysql.proc WHERE db='your_database' AND name='your_procedure';

Gives:

IN param1 VARCHAR(32), IN param2 int, ...

This requires some more work to put into a format for presentation; although a string.split function would at least tidy it up.

Upvotes: 19

Sergey Benner
Sergey Benner

Reputation: 4431

show create procedure show create function? these inbound calls require the access to the mysql.proc table that's where you have to look at and the body field is of longblob type there select cast(param_list as char) from mysql.proc where name='' and type='PROCEDURE'

Upvotes: 1

Related Questions