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.

Example:
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. Required fields are marked *

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