The Error Message

When dealing with errors, it’s often necessary to log, or pass on errors to your users. In either case, you’ve already seen how to trap the ERROR_NUMBER. This time I’ll show you how to get the ERROR_MESSAGE.

The ERROR_MESSAGE function takes no argument and it returns a string.

BEGIN TRY
	DECLARE @Number TINYINT,
	        @Result TINYINT;

	SET @Number = 252;
	SET @Result = @Number + 20;

	SELECT @Number AS Number, @Result AS Result;
END TRY
BEGIN CATCH
	PRINT ERROR_MESSAGE()
END CATCH

This would produce:

Arithmetic overflow error for data type tinyint, value = 272.

As a programmer, you probably have a good idea of what an overflow error is, and what a TINYINT is.

Unfortunately, this message may not be very clear to a regular user. That’s why I usually LOG the ERROR_MESSAGE(), but display a friendlier message to the end user.

This would look something like the following

BEGIN TRY
    DECLARE @Number TINYINT,
	    @Result TINYINT;

    SET @Number = 252;
    SET @Result = @Number + 20;

    SELECT @Number AS Number, @Result AS Result;
END TRY
BEGIN CATCH
    PRINT N'There was a problem adding 20 to your number.  Please notify your support contact.'
	--I'm assuming I have a stored procedure to write the message to the log
	sp_writeToLog ERROR_MESSAGE()
END CATCH
GO

It’s pretty simple to handle the errors, it’s only difficult in identifying where you might have errors. It’s a skill you learn as you grow. If you have any questions, send them in. I’m here to help!

By Shannon Lowder

Shannon Lowder is the Database Engineer you've been looking for! Look no further for expertise in: Business Analysis to gather the business requirements for the database; Database Architecting to design the logical design of the database; Database Development to actually build the objects needed by the business logic; finally, Database Administration to keep the database running in top form, and making sure there is a disaster recovery plan.

Leave a comment

Your email address will not be published. Required fields are marked *