Error Line,Error Severity, Error State, Error Number,Throw , Raise Error in Sql server

ERROR_LINE

The Error_Line function returns the error Line number from code. This function does not accept any parameters. The Error_Line() function is used to determine the error line which occurred in a try block. The following query returns the line number where the error occurred:

BEGIN TRY

Select 14/ 0

END TRY

BEGIN CATCH

SELECT 'An error has occurred at line ' + Cast(ERROR_line() as Varchar )

END CATCH

ERROR NUMBER

Returns the error number of the error that caused the CATCH block of a TRY…CATCH construct to be run.

This function may be called anywhere within the scope of a CATCH block.

ERROR_NUMBER returns the error number regardless of how many times it is run, or where it is run within the scope of the CATCH block. 

BEGIN TRY

Select 11/ 0

END TRY

BEGIN CATCH

SELECT 'An error has occurred at line: ' + Cast(ERROR_Number() as Varchar )

END CATCH

ERROR STATE

Returns the state number of the error that caused the CATCH block of a TRY…CATCH construct to be run.

When called in a CATCH block, returns the state number of the error message that caused the CATCH block to be run.

Returns NULL if called outside the scope of a CATCH block.

ERROR_STATE may be called anywhere within the scope of a CATCH block.

ERROR_STATE returns the error state regardless of how many times it is run, or where it is run within the scope of the CATCH block. This is in contrast to functions like @@ERROR, which only returns the error number in the statement immediately after the one that causes an error, or in the first statement of a CATCH block

BEGIN TRY

Select 11/ 0

END TRY

BEGIN CATCH

SELECT 'An error has occurred at line: ' + Cast(ERROR_State() as Varchar )

END CATCH

ERROR SEVERITY

0 to 10	Messages with a severity level of 0 to 10 are informational messages and not actual errors.
11 to 16	Severity levels 11 to 16 are generated as a result of user problems and can be fixed by the user. For example, the error message returned in the invalid update query, used earlier, had a severity level of 16.
17	Severity level 17 indicates that SQL Server has run out of a configurable resource, such as locks. Severity error 17 can be corrected by the DBA, and in some cases, by the database owner.
18	Severity level 18 messages indicate nonfatal internal software problems.
19	Severity level 19 indicates that a nonconfigurable resource limit has been exceeded.
20	Severity level 20 indicates a problem with a statement issued by the current process.
21	Severity level 21 indicates that SQL Server has encountered a problem that affects all the processes in a database.
22	Severity level 22 means a table or index has been damaged. To try to determine the extent of the problem, stop and restart SQL Server. If the problem is in the cache and not on the disk, the restart corrects the problem. Otherwise, use DBCC to determine the extent of the damage and the required action to take.
23	Severity level 23 indicates a suspect database. To determine the extent of the damage and the proper action to take, use the DBCC commands.
24	Severity level 24 indicates a hardware problem.
25	Severity level 25 indicates some type of system error.

BEGIN TRY

Select 11/ 0

END TRY

BEGIN CATCH

SELECT 'An error has occurred at line: ' + Cast(ERROR_Severity() as Varchar )

END CATCH

Severity levels 11 to 16 are generated as a result of user problems and can be fixed by the user.

--In SQL Server 2005/2008, if you want to re-throw an error in a catch block of a TRY CATCH statement, you have to use RAISERROR with ERROR_MESSAGE() and ERROR_SEVERITY(). But in SQL Server 2012 you can use only a THROW statement to re-throw an error in a catch block of a TRY CATCH statement.

--We divide a number by zero. A THROW statement is used to raise exceptions; see: 

BEGIN TRY DECLARE @VALUE INT SET @VALUE = 12/ 0

END TRY

BEGIN CATCH

throw

END CATCH

--Now press F5 to execute it; the results will be:

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

Raise Error Vs Throw

Both RAISERROR and THROW statements are used to raise an error in Sql Server. The journey of RAISERROR started from Sql Server 2005, where as the journey of THROWstatement has just began with Sql Server 2012. obviously, Microsoft suggesting us to start using THROW statement instead of RAISERROR. THROW statement seems to be simple and easy to use than RAISERROR.

RAISE ERROR	THROW
Introduced in SQL SERVER 2005	Introduced in SQL SERVER 2012.
It always generates new exception and results in the loss of the original exception details. Below example demonstrates this: BEGIN TRY   DECLARE @result INT --Generate divide-by-zero error   SET @result = 55/0 END TRY BEGIN CATCH --Get the details of the error --that invoked the CATCH block  DECLARE    @ErMessage NVARCHAR(2048),    @ErSeverity INT,    @ErState INT     SELECT    @ErMessage = ERROR_MESSAGE(),    @ErSeverity = ERROR_SEVERITY(),    @ErState = ERROR_STATE()     RAISERROR (@ErMessage,              @ErSeverity,              @ErState ) END CATCH RESULT: Msg 50000, Level 16, State 1, Line 19 Divide by zero error encountered. NOTE:The actual line number of the code which generated Divided By Zero error here is 4, but the exception message returned by RAISERROR is showiung it as 19. Also the error number corresponding to divide by zero error is 8134 in the SYS.Messages table, but the one returned by RAISERROR is 50000.	To Re-THROW the original exception caught in the TRY Block, we can just specify the THROW statement without any parameters in the CATCH block. Below example demonstrates this: BEGIN TRY   DECLARE @result INT --Generate divide-by-zero error   SET @result = 55/0 END TRY BEGIN CATCH     THROW END CATCH RESULT: RESULT: Msg 8134, Level 16, State 1, Line 4 Divide by zero error encountered. With above example it is clear that THROW statement is very simple for RE-THROWING the exception. And also it returns correct error number and line number.
In the below Batch of statements the PRINT statement after RAISERROR statement will be executed. BEGIN  PRINT 'BEFORE RAISERROR'  RAISERROR('RAISERROR TEST',16,1)  PRINT 'AFTER RAISERROR' END RESULT: BEFORE RAISERROR Msg 50000, Level 16, State 1, Line 3 RAISERROR TEST AFTER RAISERROR	In the below Batch of statements the PRINT statement after THROW statement will not executed. BEGIN     PRINT 'BEFORE THROW';     THROW 50000,'THROW TEST',1     PRINT 'AFTER THROW' END RESULT: BEFORE THROW Msg 50000, Level 16, State 1, Line 3 THROW TEST BEGIN TRY   DECLARE @RESULT INT = 55/0    END TRY BEGIN CATCH   PRINT 'BEFORE THROW';   THROW;   PRINT 'AFTER THROW' END CATCH   PRINT 'AFTER CATCH' RESULT: BEFORE THROW Msg 8134, Level 16, State 1, Line 2 Divide by zero error encountered.
Can set the severity of error	There is no severity parameter. The exception severity is always set to 16. (unless re-throwing in a CATCH block)
The statement before the THROW statement is not  followed by the semicolon (;) statement terminator.	The statement before the THROW statement must be followed by the semicolon (;) statement terminator.

CREATE PROCEDURE dbo.TryCatchTest

AS
BEGIN TRY
    SELECT 1/0
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber
     ,ERROR_SEVERITY() AS ErrorSeverity
     ,ERROR_STATE() AS ErrorState
     ,ERROR_PROCEDURE() AS ErrorProcedure
     ,ERROR_LINE() AS ErrorLine
     ,ERROR_MESSAGE() AS ErrorMessage; 

END CATCH