Get number of arguments for a stored procedure

Question

Hi!

I would like to get the the number of arguments required for a stored procedure in MySql. Is that possible?

My first thought was to try

SELECT * FROM information_schema.ROUTINES;

but therein lies no information about the number of arguments as far as I can see. Next try was

SHOW PROCEDURE STATUS;

but that just seems to return a subset of the first query.

My goal is to be able to "NULL-pad" a sproc call in PHP to avoid errors like

"Incorrect number of arguments for PROCEDURE schema.table; expected nn, got mm

To make a static lookup table would of course work, but is certainly not desireable. And sure, one could parse the message returned, alter the query and retry, but that does not seem very nice either.

Thanks in advance! /Victor

Answer 1

SHOW CREATE PROCEDURE <procedure_name>;

http://dev.mysql.com/doc/refman/5.0/en/show-create-procedure.html

Answer 2

You can retrieve the parameter list via:

SELECT DISTINCT name, param_list FROM mysql.proc WHERE db=DATABASE();