How to implement error handling in SQL Server

Question

When I apply query in Sql server I got an error I want to know how can I handle error and how to retrieving error Information?

Answer 1

Handling an error in SQL Server

To handle the error in the SQL Server. you can use one of the below methods:

1. @@Error
2. Try ... Catch block

1) @@Error in SQL

Returns the error number for the last Transact-SQL statement executed

Note: @@ERROR is reset on each statement executed

2) Try catch in SQL

In this example, the [ID] is an int type but I am trying to insert a char type that will get an error. and to know information about this error we will use the Error function as below:

BEGIN TRY
     INSERT INTO Employee([ID],[Name],[Gender],[Salary],[Department_ID]) VALUES('test','ahmed','male',2000,1)
END TRY
BEGIN CATCH
    Select 
   ERROR_NUMBER() as ErrorNumber,
   ERROR_MESSAGE() as ErrorMessage,
   ERROR_PROCEDURE() as ErrorProcedure,
   ERROR_STATE() as ErrorState,
   ERROR_SEVERITY() as ErrorSeverity,
   ERROR_LINE() as ErrorLine
END CATCH

The result