Sql-server – How to get the current line number from an executing stored procedure

sql serverstored-procedurest-sql

Is there a function or keyword that will allow me to get the current line number in a stored procedure?

I know there's an undocumented LineNo function that allows you to set the line number, and affect the output of system error messages
https://stackoverflow.com/questions/4054511/what-exactly-does-the-t-sql-lineno-reserved-word-do

I know there's a function ERROR_LINE() that's available inside of a BEGIN CATCH ... END CATCH.

ERROR_LINE() does what I need, but I want to use it from outside a CATCH block. Anywhere in the file.

 DECLARE @InsertSource VARCHAR(1000) = object_name(@@procid)
 EXEC PROC_Accounting_Transaction_Insert ... other parameters..., @InsertSource

Currently I'm just hard-coding the call # as it appears in the stored procedure body, but it's getting old quickly.

DECLARE @InsertSource VARCHAR(1000) 

SET @InsertSource = object_name(@@procid) + '#1'
EXEC ...

SET @InsertSource = object_name(@@procid) + '#2'
EXEC ...

Best Answer

This question actually came up 2.5 years ago on StackOverflow, and I posted an Answer there:

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

The relevant portion of that Answer is copied below:


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:

DECLARE @Line INT = -1; -- default to an invalid line #

-- some code

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

-- some code

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

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.