How to throw an Exception from SQL code

[Campbell Ritchie]

I need a table called "asset" which contains assets, would you believe. Now it is possible to buy an asset, so I wrote a stored procedure, and I can easily write a stored procedure for disposing of an asset (eg sold, damaged, stolen). MySQL 5.0.51 or thereabouts.

But I don't want to call the dispose_of_asset procedure twice on the same asset. Is there any way to write SQL code which will raise an SQLException on JDBC if the dispose_of_asset procedure is called on something which has already been disposed of?

I have found this by Googling, which said to select a non-existent column. Is there a more elegant way to do it.

[Paul Clapham]

Do you have to have the stored procedure throw an exception, or could you have it return one value if it did its work correctly and a different value if it failed?

[Campbell Ritchie]

It isn't actually returning a value at present, so I could return TRUE or FALSE and use ROLLBACK; if the asset has already been disposed of.

[Campbell Ritchie]

Thank you. I shall try changing it to a function with a boolean return type. Then whoever implements the JDBC bit (myself) will know to watch for that return.