SQL ERROR LINE

SQL ERROR LINE is one of the SQL System Functions that will return the line number at which the error has occurred. This ERROR_LINE function works within the scope of a SQL 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. Refer to the SQL tutorial page.

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, tinyint holds numbers from 0 to 255, which means Overflow. So, it will exit from the TRY block and execute the statements inside our CATCH block. We used the SQL 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