T-SQL – Error handling

Errors have got a few properties:
a) number: standard SQL Server errors less than 50000, custom errors greater than 50000;
b) serverity: from 0 till 25, logged to log when > 16, closes connection and transactions when > 20;
c) message.

There are two ways to create an error: RAISERROR, THROW.

RAISERROR('Test', 16,0); -- raise error with message, sererity and state
THROW 50002, 'Test', 0; -- throw error with number, message, state and constant severity equal to 16

Only errors with severity > 10 are catched in TRY-CATCH clause. When serverity is lower error message is displayed, but code continues normal execution.

BEGIN TRY
	RAISERROR('Bug found!', 10, 0); -- severity = 10
	SELECT 'Line2'; --executed
END TRY
BEGIN CATCH
	SELECT 'Line3'; -- not executed
END CATCH

BEGIN TRY
	RAISERROR('Bug found!', 11,0); -- serverity = 11
	SELECT 'Line2'; -- not executed
END TRY
BEGIN CATCH
	SELECT 'Line3'; -- executed
END CATCH

You can read information about ocurred error.

BEGIN TRY
	RAISERROR('Bug found!', 12,0);
	SELECT 'Line2';
END TRY
BEGIN CATCH
	SELECT ERROR_NUMBER() AS errornumber
, ERROR_MESSAGE() AS errormessage
, ERROR_LINE() AS errorline
, ERROR_SEVERITY() AS errorseverity
, ERROR_STATE() AS errorstate;
END CATCH
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s