Inside a proc, a insert, update or delete statement that fails will 'raise a condition'.
When you do not handle the condition, that will terminate execution of the procedure.
Let's say we have a proc like this,
create procedure p()
begin
insert
into myTable t
values (1,2,3);
end;
Suppose we'd call the proc:
call p();
Now the insert is executed, but suppose it violates a primary key or unique constraint. Your insert statement will raise a condition and the insert will not be executed:
ERROR 1062 (23000): Duplicate entry '1' for key 1
The exact message may vary, but the 1062 (mysql_error_code) and 23000 (SQLSTATE) should appear invariably for this type of error.
(see
http://dev.mysql.com/doc/refman/5.0/en/error-messages-server.html for more info on those error messages).
Now, you can handle the error by declaring a handler:
create procedure p()
begin
declare continue handler for sqlstate '23000'
select 'whoops...duplicate, sorry';
insert
into myTable t
values (1,2,3);
end;
when an error occurs the most appropriate handler is localized and the statement associated with that is executed. If it is a continue handler, execution of the procedure is resumed after the violating statement, else, in case of an EXIT handler, the block containing the handler declaration is exited.
So, you can handle errors for those you anticipate. You do so by declaring appropriate handlers, and associate the statements that deal with the error with that handler.
see
http://dev.mysql.com/doc/refman/5.0/en/conditions-and-handlers.html
There is nothing like @@error that allows you to programmatically discover what error occurred
Edited 1 time(s). Last edit at 11/11/2005 05:37PM by Roland Bouman.