T-SQL: Find line number for error inside stored procedure with sp_helptext

Just for those who didn’t know this (like myself). When you execute a SQL Server stored procedure manually, and an error occurs, SQL Server also gives information about the line number on which the error occurred.

Msg 8114, Level 16, State 5, Procedure Proc_Edreams_GetProjectsBySimpleSearch_Reporting, Line 60
Error converting data type nvarchar to bigint.

It can be difficult to determine the line 60, but not with the sp_helptext stored procedure, this breaks up the stored procedure and gives the exact line where it fails:
sp_helptext Proc_GetProjectsBySimpleSearch
screenshot sp_helptext command results

More information about the command:

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.