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!

Related posts:

1. Error Number
2. The Error Severity
3. Exception Handling
4. Nesting an Exception
5. Return a Success/Failure to a Job based on a SQL query?