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):
declare @errorMessage nvarchar(500) = 'Error occurred: ' + error_message(),
@errorSeverity smallint = error_severity(),
@errorState smallint = error_state()
raiserror(@errorMessage, @errorSeverity, @errorState)
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'
raiserror('Date out of range', 16, 1)
print '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.