CODEWITHSUNDEEP
sqlsql-servert-sqlstored-procedures
Bharat Radhakrishnan
Bharat Radhakrishnan

Reputation: 45

SQL Server : Error_Line returns line number which does not match with stored procedure line number

I have a stored procedure which is about 4000 lines long; I get a error in line number 699, but I know SSMS takes line number differently. Is there any way to find the exact line number?

Approach I have tried so far.

  1. Copy pasted the stored procedure to an editing tool and checked the line number
  2. Tried debugging but was not able to
  3. Tried this sp_helptext proc_name, but this query doesn't execute

Is there any way to find out the correct line number ?

Upvotes: 2

Views: 5156

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175964

You could check definition in sys.sql_modules:

SELECT *
FROM sys.sql_modules
WHERE object_id = OBJECT_ID('proc_name');

DBFiddle Demo

EDIT:

And with split to specific row:

SELECT s.object_id, s.definition, s2.*
FROM sys.sql_modules s
CROSS APPLY (SELECT *, ROW_NUMBER() OVER(ORDER BY 1/0) 
             FROM STRING_SPLIT(s.definition, CHAR(10))) s2(line, rn)
WHERE object_id = OBJECT_ID('test')  -- proc name
  AND rn = 5;  -- error line

DBFiddle Demo2

DBFiddle Demo3

Upvotes: 1

Related Questions