Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.

Quick Guide to RAISERROR
Published: Jan 29, 2016
Have you ever written code and you want custom error messages to be returned as you go? Or to stop execution where YOU define an error has occurred even when it’s not a real error? You can use raiserror to achieve this. This is simple syntax and quite powerful.

This is available in all versions of SQL Server 2005+ (I haven’t tried this in SQL 2000) and I use this in quite a few scenarios when SQL Server itself won’t fire up an error message and yet I want an error raised and code execution stopped based on my own criteria.

So here’s an example where we can use RAISERROR to expand on an existing error by adding some custom text around it (unnecessary, but just to prove a point):

begin try
select 1/0
end try
begin catch
declare @errorMessage nvarchar(500) = 'Error occurred: ' + error_message(),
@errorSeverity smallint = error_severity(),
@errorState smallint = error_state()

raiserror(@errorMessage, @errorSeverity, @errorState)
end catch

That’s how easy it is to use. And if you want to make a completely custom message of your own for something that SQL Server itself won’t pick up, then this is easy as well:

declare @myDate date = '2014-07-01'

if @myDate <= '2014-08-01'
('Date out of range', 16, 1)
'Date is in August'

Therefore you can now use this happily within your code to ensure that any errors are picked up as you see fit.

The main reason I find to use this is that if you have SET XACT_ABORT ON then if you raise an error above level 10 then your code will cease to execute. This means that if you want code to stop executing based on your criteria and not an actual SQL Server error then this is very powerful.

Note that if you specify an error severity of 0-10 then this will simply return a warning to you, but will not trigger an actual error, therefore won’t stop code execution, even if you have SET XACT_ABORT ON.
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

I am a SQL Server DBA, Architect, Developer, Trainer, and CEO of SQL World. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.


© Copyright 2020 SQLTraining Ltd.