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

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s