CODEWITHSUNDEEP
mysqldatabasedynamic-sql
Chota Bheem
Chota Bheem

Reputation: 1116

Get the parameter values of the stored procedure dynamically

I am creating sort of auditing functionality for all of my stored procedures. I am able to fetch the name of the parameters that stored procedure has(from : information_schema.parameters table). However, I would like to create generic code for all stored procedures that would fetch the name of the parameters and correspondingly get the value of those parameters for that invocation and log into another table.

e.g.

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `TestSP`(IN `name` VARCHAR(255), IN `userid` INT(255), IN `isnew` VARCHAR(11))
BEGIN

#DECLARE exit handler for sqlexception  ROLLBACK;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;

INSERT INTO app_db_log(TYPE,MESSAGE, INFO) VALUES ('ERROR','An error has occurred, operation rollback and the stored procedure was terminated',<INSERT ALL THE PARAMETER VALUES AS SINGLE STRING HERE> );
COMMIT;

SELECT 'An error has occurred, operation rollback and the stored procedure was terminated';
END;

START TRANSACTION;
SIGNAL SQLSTATE '45000';
COMMIT;
END$$
DELIMITER ;

Thanks in advance!

Upvotes: 0

Views: 185

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562270

You can get the list of parameters for the routine from INFORMATION_SCHEMA.PARAMETERS, but you'd have to know the schema and name of the procedure:

mysql> delimiter $$

mysql> create procedure myproc (in foo int, in bar int)
    -> begin
    -> select group_concat(parameter_name order by ordinal_position) as params
    -> from INFORMATION_SCHEMA.PARAMETERS
    -> where specific_schema='test' and specific_name='myproc';
    -> end$$

mysql> call myproc(123, 456)$$
+---------+
| params  |
+---------+
| foo,bar |
+---------+

You would need to use dynamic SQL to do this, but you can't reference stored proc parameters in dynamic SQL:

Demonstration:

mysql> create procedure myproc (in foo int, in bar int)
    -> begin
    -> set @sql = 'SELECT foo, bar';
    -> prepare stmt from @sql;
    -> execute stmt;
    -> end$$

mysql> call myproc(123, 456)$$
ERROR 1054 (42S22): Unknown column 'foo' in 'field list'

I generally discourage people from using MySQL stored procedures. This task would be far easier in virtually any application programming language.

Upvotes: 1

Related Questions