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

Question

When I use SQL Server and there's an error, the error message gives a line number that has no correlation to the line numbers in the stored procedure. I assume that the difference is due to white space and comments, but is it really?

How can I relate these two sets of line numbers to each other? If anyone could give me at least a pointer in the right direction, I'd really appreciate it.

I'm using SQL server 2005.

Answer 1

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.