SQL Server stored procedure line number issue

The line numbers outputted by sp_helptext are totally different than the line number you see in the error message.

For instance, in my stored procedure error it said the error occured on line number 194 but actually my stored procedure showed only 136 lines when I showed sp_helptext. BTW, I used SQL Server 2008.

It's the 9th line from the CREATE PROCEDURE statement. A SQL statement is often multiline so "line 9" will refer to the first line of the statement (eg INSERT or UPDATE)

However, if you have comments above the CREATE PROCEDURE or blank lines before it then you can't rely on this... so run ALTER PROC with ALTER PROC as first line in the batch.

A tip I learnt from another answer ...

If you do

sp_helptext procedure_name

SQL will output its 'remembered' version of the create procedure statement, and that is what it gets it line numbers from apparently. If you have SSMS in 'grid output' mode then it will also output the line numbers (as the row numbers of the result set).

NB: in my case it was working from the CREATE PROCEDURE statement plus a bunch of comments above it, so line 1 was about 6 lines above the CREATE PROCEDURE call.