5

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 ...
  • For your intended purpose, I would think that you are better off with your current implementation of specifying #1, etc as those markers will still be relevant as you add/remove/change code in this Stored Proc (assuming you never re-assign one of those call numbers). – Solomon Rutzky May 19 '16 at 15:57
3

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.

4

Adam Machanic sent me this in email

DECLARE @lineno INT
BEGIN TRY SET @lineno = 1/0 END TRY BEGIN CATCH SET @lineno = ERROR_LINE() END CATCH

It does qualify as a one line in my book.

Per @ScottGartner, a true one-liner.

BEGIN TRY print 1/0; END TRY BEGIN CATCH print 'Line Number: ' + CAST(ERROR_LINE() as nvarchar(20)); END CATCH;
  • 1
    Interestingly enough, I just remembered (and found) that I had posted this a couple of years ago on StackOverflow: SQL print line number in comment of dynamically created stored procedure? ;-) – Solomon Rutzky May 21 '16 at 15:10
  • 1
    @srutzky Cross post your answer and I will mark correct. – JJS May 23 '16 at 17:41
  • 1
    Here's an simpler version if all you want to do is print it out: BEGIN TRY print 1/0; END TRY BEGIN CATCH print 'Line Number: ' + CAST(ERROR_LINE() as nvarchar(20)); END CATCH; – Scott Gartner Apr 30 at 19:07
  • 1
    @ScottGartner that's a great alternative. – JJS Apr 30 at 19:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.