The discussion as to what is the best method is not very interesting. All the work-around methods discussed so far (unique constraint violation see:
http://mysql.gilfster.com/page.php?parent_id=2&page_id=2.0.7; UDF see:
http://rpbouman.blogspot.com/2005/11/using-udf-to-raise-errors-from-inside.html; non-existing stored proc: this thread) essentially boil down to deliberately causing an error or constraint violation, and inevitably, the block or caller that wants to handle it is stuck figuring out what the REAL problem might be (as Andrew pointed out already).
If you still want to weigh the advantages and disadvantages for the workarounds, I feel one should consider the following criteria:
1) how much chance is there make a mistake in handling the error
Because all the workarounds revolve around abusing another constraint or condition, there is a reasonable risk of handling inappropriately to the raised condition. Unfortunately, because the error code or error message is not visible inside a mysql proc (anyone care to add a comment on this feature request? It might help:
http://bugs.mysql.com/bug.php?id=11660) neither of the workarounds by itself are very good in this respect. Just relying on the abilities of the client to read out the error code or error message is no good: this would leave the responsibility to handle the errors with the application layer - often not a good place.
A solution -sort of- can be enabled for all these workarounds however: by packing the actual error raising device inside a stored procedure (somewhat like RAISE_APPLICATION_ERROR in oracle), you can set one or multiple user variables (such as: @error_code, @error_message or @sqlstate, @offending_proc, @offending_block, knock yourself out...) just prior to actually raising the error. These can then be checked by whatever handler. Of course, these variables should be reserved for this purpose only, and be cleaned up after handling the error. In the UDF article, I suggested such a mechanism (at the bottom) but it should work equally well for the other workarounds as well.
2) how much dependencies are involved to set the workaround up
Here, Zigmund's method seems to have an edge as it requires absolutely no work in advance to make it work. The UDF involves of course some work, but after deploying it, it works for every database. For the unique violation, each database needs to set up it's own table - or stored procedure - or at least, the privileges need to be granted to execute the sp (BTW, does anyone know if you can grant something to everyone - something like the grant blabla to PUBLIC in oracle? ).
Of course, when we want to be able to actually handle the error we raised, we will need stored procedure encapsulation (as suggested under 1) anyway, so we're stuck with the privilege thing regardless of the workaround.
On the other hand, Zigmund's method needs a disciplined set of developers to work. It should remain clear what stored procedures are in fact mockups to get the workaround working.
Zigmund, maybe you'll care to explain WHY your method is better? You certainly seem to have a strong feeling about it...