Exception Handling / Error Handling

We can handle error in SQL Server using below 3 methods,

Error / Exception -

System defined

User Defined 

1) Try Catch Block

2) RaisError()

3) Throw()

Try Catch Block

begin Try

Select 1/0

End Try

begin catch

print Error_message()

--log

End catch

A group of T-SQL statements can be enclosed in TRY block. 

If an error occurs in the TRY block, 

control is passed to CATCH block (group of SQL statements that is enclosed)

In the scope of a CATCH block, 

the below system functions can be used to obtain information about the error,

ERROR_NUMBER(): Returns the Error Number.

ERROR_SEVERITY(): Returns the Severity of the Error.

ERROR_STATE(): Returns the State of the Error.

ERROR_PROCEDURE(): Returns the name of the SP/UDF in which the error occurred.

ERROR_LINE(): Returns the line number of the SQL statement that raised the error.

ERROR_MESSAGE(): Returns the error message.

Select * from SYS.Messages

1. ERROR NUMBER

Any error number <= 50000 is a System Defined Message and the ones that are > 50000 are User Defined Messages. 

SYS.Messages catalog view can be used to retrieve both System and User Defined Messages. 

We can add a user defined message using sp_addmessage and we can remove it using the system stored procedure sp_dropmessage. 

2. ERROR SEVERITY

Error Severity can be between 0-25.

0-10: Informational or a warning.

11-16: Programming Errors.

17-25: Resource / Hardware / OS/ SQL Server Internal Errors.

20-25: Terminates the Connection.

19-25: Only User with SysAdmin rights can raise errors with this severity.

3. ERROR STATE

The same error can be raised for several different conditions in the code. 

Each specific condition that raises the error assigns a unique state code. 

Also the SQL Support team uses it to find the location in the source code where that error is being raised.

4. ERROR PROCEDURE

The name of the Stored Procedure or the function in which the error occurred. It will be blank if it is a normal batch of statements.

5. ERROR LINE

Line number of the statement within a SP, function or Batch that triggered the error. 

6. ERROR MESSAGE

Error description detailing the reason for the error.

--------------------------

Log the Error and write error into Errorlog Table.

Errorlog Table Defination:

Drop Table if Exists [dbo].[ErrorLogHandle] 

CREATE TABLE [dbo].[ErrorLogHandle](

[Id] [bigint] IDENTITY(1,1) PRIMARY Key,

[ErrorNumber] [bigint] NULL,

[ErrorLine] [int] NULL,

[ErrorProcedure] [nvarchar](100) NULL,

[ErrorMessage] [nvarchar](max) NOT NULL,

[ErrorDateTime] [datetime] NOT NULL,

 )

--1) Handle Error in stored procedure

Select * from [dbo].[ErrorLogHandle]

create procedure [dbo].[Proc_ExceptionHandle]

AS

BEGIN

BEGIN TRY 

Print 'Hi'

Select 15/0 As Result;

END TRY 

BEGIN CATCH 

INSERT INTO [dbo].[ErrorLogHandle]

([ErrorNumber],[ErrorLine],[ErrorProcedure],[ErrorMessage],[ErrorDateTime])

SELECT   ERROR_NUMBER()    AS ErrorNumber,  

ERROR_LINE()      AS ErrorLine, 

ERROR_PROCEDURE() AS ErrorProcedure, 

ERROR_MESSAGE()   AS ErrorMessage,

GETDATE()        AS ErrorDateTime

END CATCH 

END

Exec [dbo].[Proc_ExceptionHandle]

output  

Result 

------------

here we can check error in logged in ErrorLogHandle Table.

Select * from ErrorLogHandle

Raiserror method

Raise user defined exception.

RaisError(msg_str/msg_id, severity, state)

If a msg_id is passed to RAISERROR, the ID must be defined in sys.messages.

The msg_str parameter can contain printf formatting styles.

The severity parameter specifies the severity of the exception.

Create procedure [dbo].[Proc_Raiseerror]

AS

BEGIN

BEGIN TRY 

Declare @yearlyPackage Int

Set @yearlyPackage=200000 

Declare @MonthlySalary Decimal(8,3)

Set @MonthlySalary=(@yearlyPackage/12);

Select @MonthlySalary

if (@MonthlySalary<=25000)

BEGIN

RaisError('Salary Should be greater than 25000',16,1);

END

Select 'My statement' as Stmt

END TRY 

BEGIN CATCH  

INSERT INTO [dbo].[ErrorLogHandle]

([ErrorNumber],[ErrorLine],[ErrorProcedure],[ErrorMessage],[ErrorDateTime])

SELECT   ERROR_NUMBER()    AS ErrorNumber,  

ERROR_LINE()      AS ErrorLine, 

ERROR_PROCEDURE() AS ErrorProcedure, 

ERROR_MESSAGE()   AS ErrorMessage,

GETDATE()        AS ErrorDateTime

---in catch block we can use RAISERROR

END CATCH 

END

exec dbo.Proc_Raiseerror

Select * from ErrorLogHandle

Throw method

Raises an exception 

Arguments:

1) error_number

Is a constant or variable that represents the exception. 

error_number is int and must be greater than or equal to 50000 and less than or equal to 2147483647.

2) message

Is an string / variable that describes the exception. 

message is nvarchar(2048).

3) state

Is a constant / variable between 0 and 255 that indicates the state to associate with the message. state is tinyint.

The statement before the THROW statement must be followed by the semicolon (;) statement terminator.

BEGIN Try

Declare @yearlyPackage Int

Set @yearlyPackage=200000  

Declare @MonthlySalay Decimal(8,3)

Set @MonthlySalay=(@yearlyPackage/12);

Select @MonthlySalay

IF @MonthlySalay<25000

BEGIN

;THROW 50000,'Salary Should be greater than 25000', 1;

END

Select 'Hello'

End try

Begin catch

INSERT INTO [dbo].[ErrorLogHandle]

([ErrorNumber],[ErrorLine],[ErrorProcedure],[ErrorMessage],[ErrorDateTime])

SELECT   ERROR_NUMBER()    AS ErrorNumber,  

ERROR_LINE()      AS ErrorLine, 

ERROR_PROCEDURE() AS ErrorProcedure, 

ERROR_MESSAGE()   AS ErrorMessage,

GETDATE()        AS ErrorDateTime

end catch

Select * from ErrorLogHandle