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!