CODEWITHSUNDEEP
mysqlfunctionstored-proceduresinformation-schema
adam
adam

Reputation: 303

Get current executing stored procedure name in MySQL?

I need a log for the stored procedures i have written in MySQL. I know this is available in MS SQL Server as @@procid.

What is the equivalent in MySQL?

I'm going to use a timestamp, connection_id, database().

How can i get the name of the sp i am executing? How about the sp that called me?

thanks, adam

Upvotes: 5

Views: 4701

Answers (1)

Devart
Devart

Reputation: 121932

You can pass a procedure name as an IN parameter into called procedures, and log this information from these procedures.

For example -

DELIMITER $$

CREATE PROCEDURE procedure1(IN proc_name VARCHAR(255))
BEGIN
  INSERT INTO proc_log VALUES('procedure1', proc_name, NOW());
END$$

CREATE PROCEDURE procedure2(IN proc_name VARCHAR(255))
BEGIN
  INSERT INTO proc_log VALUES('procedure2', proc_name, NOW());
  CALL procedure1('procedure2');
END$$

DELIMITER ;

CALL procedure2(NULL);

SELECT * FROM proc_log;
+------------+----------------+---------------------+
| proc_name  | call_proc_name | call_ts             |
+------------+----------------+---------------------+
| procedure2 | NULL           | 2012-07-30 16:17:53 |
| procedure1 | procedure2     | 2012-07-30 16:17:53 |
+------------+----------------+---------------------+

Upvotes: 1

Related Questions