How to Get the Actual Stored Procedure Line Number from an Error Message

SQL Server stored procedure line number issue

It's the 9th line from the CREATE PROCEDURE statement. A SQL statement is often multiline so "line 9" will refer to the first line of the statement (eg INSERT or UPDATE)

However, if you have comments above the CREATE PROCEDURE or blank lines before it then you can't rely on this... so run ALTER PROC with ALTER PROC as first line in the batch.

SQL Server : Error_Line returns line number which does not match with stored procedure line number

You could check definition in sys.sql_modules:

SELECT *
FROM sys.sql_modules
WHERE object_id = OBJECT_ID('proc_name');

DBFiddle Demo

---

EDIT:

And with split to specific row:

SELECT s.object_id, s.definition, s2.*
FROM sys.sql_modules s
CROSS APPLY (SELECT *, ROW_NUMBER() OVER(ORDER BY 1/0) 
             FROM STRING_SPLIT(s.definition, CHAR(10))) s2(line, rn)
WHERE object_id = OBJECT_ID('test')  -- proc name
  AND rn = 5;  -- error line

DBFiddle Demo2

DBFiddle Demo3

SQL print line number in comment of dynamically created stored procedure?

You can use TRY / CATCH with a forced error as the CATCH block can return the line number that the error occurred on via the ERROR_LINE() function. The full construct, formatted for readability, is:

BEGIN TRY
    ;THROW 50000, 'Line#', 1 -- all 3 values are arbitrary, but required
END TRY
BEGIN CATCH
    SET @LineNumber = ERROR_LINE()
END CATCH

Now, to get the @LineNumber variable to populate with the line number that it is being set on, you can reduce that construct to a single line as follows:

BEGIN TRY;THROW 50000,'',1;END TRY BEGIN CATCH;SET @Line=ERROR_LINE();END CATCH

Here is a full example of it working:

SET ANSI_NULLS ON
SET NOCOUNT ON
GO
-- Line #1 (of current batch, not of the entire script if GOs are used)

DECLARE @CRLF NCHAR(2) = NCHAR(13) + NCHAR(10),
        @SQL1 NVARCHAR(MAX) = '',
        @SQL2 NVARCHAR(MAX) = '', -- Line #5
        @Line INT = -1 -- default to an invalid line #

SET @SQL1 += N'/********************' + @CRLF
SET @SQL1 += N' *' + @CRLF
SET @SQL1 += N' * Test Auto-' + @CRLF -- Line #10
SET @SQL1 += N' * Generated Proc 1' + @CRLF
BEGIN TRY;THROW 50000,'',1;END TRY BEGIN CATCH;SET @Line=ERROR_LINE();END CATCH
SET @SQL1 += N' * Line #:' + CONVERT(NVARCHAR(10), @Line) + @CRLF
SET @SQL1 += N' *' + @CRLF
SET @SQL1 += N' ********************/' + @CRLF -- Line #15

-- more code here

SET @SQL2 += N'/********************' + @CRLF
SET @SQL2 += N' *' + @CRLF -- Line #20
SET @SQL2 += N' * Test Auto-' + @CRLF
SET @SQL2 += N' * Generated Proc 2' + @CRLF
BEGIN TRY;THROW 50000,'',1;END TRY BEGIN CATCH;SET @Line=ERROR_LINE();END CATCH
SET @SQL2 += N' * Line #:' + CONVERT(NVARCHAR(10), @Line) + @CRLF
SET @SQL2 += N' *' + @CRLF -- Line #25
SET @SQL2 += N' ********************/' + @CRLF

PRINT @SQL1
PRINT @SQL2
GO

The line numbers returned for Proc 1 and Proc 2 are 12 and 23 respectively, which is correct for both.

Please note that the THROW command started in SQL Server 2012. If you are using SQL Server 2005, 2008, or 2008 R2, then you need to use RAISERROR() function instead of THROW.

SQL Server 2005 stored procedure error - Line numbers don't match management studio

SSMS line numbers may not match the number returned in the error always, this is because when the SQL server executes a stored proc, it has its own compilation & execution methodology, so the line numbers vary based on the execution order it takes. For example, it may some times show the line number of the previous statement but the actual error can be in the next line, few times the error line number is shown as the from clause where as the actual error might be in one of the select columns.

Most of the times, when debugging such errors, I execute the content of the stored procedure as a block with debugging enabled in the SQL server. This always helps me to verify every line in the stored proc.

Is there a way to get the line number where an exception was thrown?

You need 10g to use

DBMS_OUTPUT.put_line('Error in '|| $$plsql_unit || ' at ' || $$plsql_line);

also look into using

DBMS_UTILITY.format_error_backtrace

there is an article in Oracle Magazine from april '05 by Steven Feuerstein:

http://www.oracle.com/technetwork/issue-archive/2005/05-mar/o25plsql-093886.html

Cheers, niels

SQL Server: how to find the specific line of failing code based on [Batch Line Start] error

Took me a while to understand your question completely. The line number mentioned in the error message does not point to the exact line in which error occurred. It points to the statement in which the error occurred. Another thing to note is that, in your stored procedure, if you have blank rows at the beginning of the query, they will be added into the line count. This sometimes throws off the line number which you see in the error message as well.

I also found an interesting read on this topic:

http://tomaslind.net/2013/10/15/line-numbers-in-t-sql-error-messages/

This should be able to clarify most of your doubts. Let me know if you need further clarifications.

Possible to get the line number of the currently executing sproc in SQL Server?

You can use something like

SELECT
    CASE
        WHEN statement_end_offset = -1
        THEN text
        ELSE SUBSTRING(text,statement_start_offset/2,(statement_end_offset- statement_start_offset)/2)
    END,
    statement_end_offset, statement_start_offset
FROM    sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
WHERE session_id = 53 --Or whatever!

The statement_start_offset and statement_end_offset are character offsets that map to the statement currently being executed rather than a line number.

A single line can contain multiple executable statements and a single statement can span multiple lines.

DB2 LUW - Get Error Line in Stored Procedure

If your Db2-server platform is Linux/Unix/Windows, and you are using a recent version, then consider using DBMS_UTILITY.FORMAT_ERROR_BACKTRACE which may help you.

Documentation here. The documentation includes a worked example.

When using this for stored procedures or routines, it is wise to always create those routines with a meaningful specific name with the SPECIFIC clause on the create or replace statement. Otherwise the routine will have a system generated name which will not be meaningful to users when it appears in the output of DBMS_UTILITY.FORMAT_ERROR_BACKTRACE. There are other reasons you should always use a specific name for your routines.

The SQLCA is for calling programs (i.e. the program that calls the stored procedure).

---

Related Topics