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 theCATCH
block can return the line number that the error occurred on via theERROR_LINE()
function. The full construct, formatted for readability, is: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: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 useRAISERROR()
function instead ofTHROW
.