EXEC sys.sp_addmessage 50001, 16, 'WOAH! What did you click on?', @replace= 'REPLACE' ; BEGIN TRY select 1/0 PRINT 'printed 1/0=Seriously?' END TRY BEGIN CATCH /*ANOTHER METHOD*/ DECLARE @ErMessage NVARCHAR(2048),@ErSeverity INT,@ErState INT SELECT @ErMessage = ERROR_MESSAGE(),@ErSeverity = ERROR_SEVERITY(),@ErState = ERROR_STATE() RAISERROR (@ErMessage,@ErSeverity,@ErState ) --Throw 50000,'Sorry...the database is trying to lift a whale now.',1 --MS Recommends using Throw in SQL2014+ RAISERROR(50001,16,1) --I like good old Raiserror for now. END CATCH
Crediting Basa for neat details, check out his blog is full of good things.
The idea is to control the message returned to the app front-end, rollback the transaction if need be or retry with another command.
More on this…with how to log the activity as well.