SQL ERROR LINE

SQL ERROR LINE is one of the SQL System Function which will return the line number at which the error has occurred. This ERROR LINE function works within the scope of a TRY CATCH block.

For example, we write a series of statements inside the TRY block. If the server finds an error, then SQL ERROR_LINE() inside the CATCH block will execute and returns the corresponding line number at which error occurred. ERROR LINE is handy to debug the issue. The basic syntax of the Error_Line in SQL Server is

ERROR_LINE()

SQL ERROR LINE example

In this example, we are going to show you how to use the Error_Line function to return the line number at which the error has occurred.

BEGIN TRY
    DECLARE @Number tinyint,
	    @Result tinyint;
    SET @Number = 252;
    SET @Result = @Number + 10;
SELECT @Number AS Number, 
       @Result AS Result;
END TRY
BEGIN CATCH
  PRINT N'Error Line = ' + CAST(ERROR_LINE() AS nvarchar(100));
  PRINT N'Error Message = ' + CAST(ERROR_MESSAGE() AS nvarchar(100));
END CATCH
SQL ERROR LINE Example 1

First, we declared two SQL Server tinyint variables

DECLARE @Number tinyint, 
         @Result tinyint;

Next we assigned 252 to @Number variable, and then we added 10 to that variable which will become 262 (Result).

SET @Number = 252; SET 
@Result = @Number + 10;

As we all know, that tinyint holds numbers from 0 to 255 means Overflow. So, it will exit from the TRY block, and execute the statements inside our CATCH block. We used the ERROR_MESSAGE function to display the error description

PRINT N'Error Line = ' + CAST(ERROR_LINE() AS nvarchar(100)); 
PRINT N'Error Message = ' + CAST(ERROR_MESSAGE() AS nvarchar(100));

ERROR LINE example 2

In this SQL error line example, we are going to find the result of integer /0.

BEGIN TRY
    DECLARE @Number int,
	    @Result int;
    SET @Number = 252;
    SET @Result = @Number / 0;
SELECT @Number AS Number, 
       @Result AS Result;
END TRY
BEGIN CATCH
  PRINT N'Error Line = ' + CAST(ERROR_LINE() AS nvarchar(100));
  PRINT N'Error Message = ' + CAST(ERROR_MESSAGE() AS nvarchar(100));
END CATCH
SQL ERROR LINE Example 2

Within the TRY block we are finding the result of 252/0

SET @Result = @Number / 10;

As we all know that the above statement will throw an error so, statement inside the CATCH block will be executed.

Categories SQL