Today, I'm gonna talk about how to handle error in SQL Server using THROW. In versions prior to SQL Server 2012, you can use @@RAISE_ERROR to generate error messages dynamically or using the sys.messages catalog.

Consider the following example:

SELECT ROUND(800.0, -3)

On executing this statement, you get the following error:

This error is caused by the value does not fit into the decimal data type.

You can use @@RAISERROR to raise a message:

BEGIN TRY
SELECT ROUND(800.0, -3)
END TRY
BEGIN CATCH
DECLARE @ErrorMsg nvarchar(1000), @Severity int
SELECT @ErrorMsg = ERROR_MESSAGE(),
@Severity = ERROR_SEVERITY()
RAISERROR (@ErrorMsg, @Severity, 1)
END CATCH

Note: The old syntax of RAISERROR syntax specifying the error number and message number got deprecated (RAISEERROR 50005 ‘Exception Occurred’). Instead the new syntax RAISEERROR(50005, 10, 1) allowed you to specify the messageid, severity and state). For new applications use THROW.

However in SQL Server 2012 and above, there’s a better way to this without much efforts, using THROW. Consider the following code:

BEGIN TRY
SELECT ROUND(800.0, -3)
END TRY
BEGIN CATCH
THROW
END CATCH

As you can see, with just one word THROW, we were able to handle the error with grace and get a result too.

HostForLIFE.eu SQL Server 2016 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.