SQL Server stored procedure line number issue

Question

I am using SQL Server 2008 Enterprise. I met with issue which says line 9 of stored procedure foo is meeting with dead lock issue. My question is how to find exactly the 9th line of the stored procedure?

My confusion is because of coding format issue, how to locate 9th line correctly.

thanks in advance, George

Answer 1

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.

Answer 2

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.