How can I get the actual stored procedure line number from an error message?

Question

IIRC, it starts counting lines from the start of the batch that created that proc. That means either the start of the script, or else the last "GO" statement before the create/alter proc statement.

An easier way to see that is to pull the actual text that SQL Server used when creating the object. Switch your output to text mode (CTRL-T with the default key mappings) and run

sp_helptext proc_name

Copy paste the results into a script window to get syntax highlighting etc, and use the goto line function (CTRL-G I think) to go to the error line reported.

Out of habit I place LINENO 0 directly after BEGIN in my stored procedures. This resets the line number - to zero, in this case. Then just add the line number reported by the error message to the line number in SSMS where you wrote LINENO 0 and bingo - you have the error's line number as represented in the query window.

If you use a Catch Block and used a RAISERROR() for any code validation within the Try Block then the Error Line gets reported where the Catch Block is and not where the real error occurred. I used it like this to clear that up.

BEGIN CATCH
  DECLARE @ErrorMessage NVARCHAR(4000);
  DECLARE @ErrorSeverity INT;
  DECLARE @ErrorState INT;

  SELECT 
     @ErrorMessage = ERROR_MESSAGE() + ' occurred at Line_Number: ' + CAST(ERROR_LINE() AS VARCHAR(50)),
     @ErrorSeverity = ERROR_SEVERITY(),
     @ErrorState = ERROR_STATE();

  RAISERROR (@ErrorMessage, -- Message text.
     @ErrorSeverity, -- Severity.
     @ErrorState -- State.
  );

END CATCH

Actually this Error_number() works very well.

This function starts counts from the last GO (Batch Separator) statement, so if you have not used any Go spaces and it is still showing a wrong line number - then add 7 to it, as in stored procedure in line number 7 the batch separator is used automatically. So if you use select Cast(Error_Number()+7 as Int) as [Error_Number] - you will get the desired answer.

In TSQL / Stored Procedures

You may get an error such as:

Msg 206, Level 16, State 2, Procedure myproc, Line 177 [Batch Start Line 7]

This means that the error is on line 177 in the batch. Not 177 in the SQL. You should see what line number your batch starts on, in my case [7], and then you add that value to the line number to find what statement is wrong