TRY/CATCH Custom Message – SQL Error Handling

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.

Thanks,
Hiram