MySQL Stored Procedure Error Handling

by

I believe there is nothing currently available in MySQL that allows access to the SQLSTATE of the last executed statement within a MySQL stored procedure. This means that … it is hard/impossible to derive the exact nature of the error.

Luckily that is not true.

SHOW ERRORS LIMIT 1   -- for SQL-state > 2
SHOW WARNINGS LIMIT 1 -- for SQL-state 1,2

Will show the last error or warning.

In order to prevent listing each and every error, you can handle a class of SQL-errors like so:

SQLWARNING is shorthand for the class of SQLSTATE values that begin with ’01’.

NOT FOUND is shorthand for the class of SQLSTATE values that begin with ’02’. This is relevant only within the context of cursors and is used to control what happens when a cursor reaches the end of a data set. If no more rows are available, a No Data condition occurs with SQLSTATE value 02000. To detect this condition, you can set up a handler for it (or for a NOT FOUND condition). An example is shown in Section 12.7.5, “Cursors”. This condition also occurs for SELECT … INTO var_list statements that retrieve no rows.

SQLEXCEPTION is shorthand for the class of SQLSTATE values that do not begin with ’00’, ’01’, or ’02’.

So to handle an exception, you need to only do:

DECLARE EXIT HANDLER FOR SQLSTATE SQLEXCEPTION .....;

Links:
http://dev.mysql.com/doc/refman/5.5/en/signal.html
http://dev.mysql.com/doc/refman/5.0/en/declare-handler.html

More Related Contents:

  1. Stored procedure that Automatically delete rows older than 7 days in MYSQL
  2. Can I create view with parameter in MySQL?
  3. Pass array to MySQL stored routine
  4. Connect By Prior Equivalent for MySQL
  5. How to raise an error within a MySQL function
  6. MySQL stored procedure vs function, which would I use when?
  7. MySQL stored procedures use them or not to use them
  8. Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation ‘=’
  9. MySQL create stored procedure syntax with delimiter
  10. mysql dynamic query in stored procedure
  11. Writing optional parameters within stored procedures in MySQL? [duplicate]
  12. Dynamic table names in stored procedure function
  13. List of Stored Procedures/Functions Mysql Command Line
  14. passing LIMIT as parameters to MySQL sproc
  15. mysql stored procedure that calls itself recursively
  16. How do you debug MySQL stored procedures?
  17. How can I use transactions in my MySQL stored procedure?
  18. MySQL Stored Procedures not working with SELECT (basic question)
  19. use a variable for table name in mysql sproc
  20. Split a string and loop through values in MySQL stored procedure
  21. How to schedule a stored procedure in MySQL
  22. My SQL Dynamic query execute and get ouput into a variable in stored procedure
  23. MySQL : When stored procedure parameter name is the same as table column name
  24. What is dynamic SQL?
  25. How to use Table output from stored MYSQL Procedure
  26. How do I write an SP in phpMyAdmin (MySQL)?
  27. Creating MYSQL Procedure in Laravel 4 Migrations
  28. is it possible to call a sql script from a stored procedure in another sql script?
  29. Mysql stored procedure don’t take table name as parameter
  30. How to pass a variable to a IN clause?

Leave a Comment