How can I get the actual stored procedure line number from an error message?

When I use SQL Server and there's an error, the error message gives a line number that has no correlation to the line numbers in the stored procedure. I assume that the difference is due to white space and comments, but is it really?

How can I relate these two sets of line numbers to each 开发者_运维百科other? If anyone could give me at least a pointer in the right direction, I'd really appreciate it.

I'm using SQL server 2005.

IIRC, it starts counting lines from the start of the batch that created that proc. That means either the start of the script, or else the last "GO" statement before the create/alter proc statement.

An easier way to see that is to pull the actual text that SQL Server used when creating the object. Switch your output to text mode (CTRL-T with the default key mappings) and run

sp_helptext proc_name

Copy paste the results into a script window to get syntax highlighting etc, and use the goto line function (CTRL-G I think) to go to the error line reported.

Out of habit I place LINENO 0 directly after BEGIN in my stored procedures. This resets the line number - to zero, in this case. Then just add the line number reported by the error message to the line number in SSMS where you wrote LINENO 0 and bingo - you have the error's line number as represented in the query window.

If you use a Catch Block and used a RAISERROR() for any code validation within the Try Block then the Error Line gets reported where the Catch Block is and not where the real error occurred. I used it like this to clear that up.

BEGIN CATCH
  DECLARE @ErrorMessage NVARCHAR(4000);
  DECLARE @ErrorSeverity INT;
  DECLARE @ErrorState INT;

  SELECT 
     @ErrorMessage = ERROR_MESSAGE() + ' occurred at Line_Number: ' + CAST(ERROR_LINE() AS VARCHAR(50)),
     @ErrorSeverity = ERROR_SEVERITY(),
     @ErrorState = ERROR_STATE();

  RAISERROR (@ErrorMessage, -- Message text.
     @ErrorSeverity, -- Severity.
     @ErrorState -- State.
  );

END CATCH

Actually this Error_number() works very well.

This function starts counts from the last GO (Batch Separator) statement, so if you have not used any Go spaces and it is still showing a wrong line number - then add 7 to it, as in stored procedure in line number 7 the batch separator is used automatically. So if you use select Cast(Error_Number()+7 as Int) as [Error_Number] - you will get the desired answer.

In TSQL / Stored Procedures

You may get an error such as:

Msg 206, Level 16, State 2, Procedure myproc, Line 177 [Batch Start Line 7]

This means that the error is on line 177 in the batch. Not 177 in the SQL. You should see what line number your batch starts on, in my case [7], and then you add that value to the line number to find what statement is wrong

you can use this

CAST(ERROR_LINE() AS VARCHAR(50))

and if you want to make error log table you can use this :

INSERT INTO dbo.tbname( Source, Message) VALUES ( ERROR_PROCEDURE(), '[ ERROR_SEVERITY : ' + CAST(ERROR_SEVERITY() AS VARCHAR(50)) + ' ] ' + '[ ERROR_STATE : ' + CAST(ERROR_STATE() AS VARCHAR(50)) + ' ] ' + '[ ERROR_PROCEDURE : ' + CAST(ERROR_PROCEDURE() AS VARCHAR(50)) + ' ] ' + '[ ERROR_NUMBER : ' + CAST(ERROR_NUMBER() AS VARCHAR(50)) + ' ] ' +  '[ ERROR_LINE : ' + CAST(ERROR_LINE() AS VARCHAR(50)) + ' ] ' + ERROR_MESSAGE())

Helpful article on this issue:

http://tomaslind.net/2013/10/15/line-numbers-in-t-sql-error-messages/

"If you instead generate the script with Management Studio, the USE dbname statements and the settings for ANSI_NULLS and QUOTED_IDENTIFIER are added automatically. Remove these statements (9 rows) to get the line numbers correct in the script window:"

The long answer: the line number is counted from the CREATE PROCEDURE statement, plus any blank lines or comment lines you may have had above it when you actually ran the CREATE statement, but not counting any lines before a GO statement…

I found it much easier to make a stored proc to play around with to confirm:

GO

-- =============================================
-- Author:          <Author,,Name>
-- Create date: <Create Date,,>
-- Description:     <Description,,>
-- =============================================
CREATE PROCEDURE ErrorTesting
       -- Add the parameters for the stored procedure here
AS
BEGIN
       -- SET NOCOUNT ON added to prevent extra result sets from
       -- interfering with SELECT statements.
       SET NOCOUNT ON;

       -- Insert statements for procedure here
       SELECT 1/0

END
GO

After you’ve created it, you can switch it to ALTER PROCEDURE and add some blank lines above the comments and above and below the first GO statement to see the effect.

One very strange thing I noticed was that I had to run EXEC ErrorTesting in a new query window instead of highlighting it at the bottom of the same window and running… When I did that the line numbers kept going up! Not sure why that happened..

you can get error message and error line in catch block like this:

'Ms Sql Server Error: - ' + ERROR_MESSAGE() + ' - Error occured at: ' + CONVERT(VARCHAR(20),  ERROR_LINE())

Just add the following code to your Stored procedure, to indicate the absolute line start number WITH "LINENO xx", where "xx" is the actual line number when you open the SP in SQL Mgt Studio

For example,

USE [Northwind]
GO
/****** Object:  StoredProcedure [automate].[workorders_exceptions_generate]    Script Date: 03/03/2021 8:49:23 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*

Here are some comments here
Which are white spaces
But the actual line after the "BEGIN" statement is 21

*/
CREATE PROCEDURE dbo.something
    @ChildWOID varchar(30)
    , @DontByPass bit = 0
    , @BillingStatus varchar(30) = null OUTPUT
AS
BEGIN
    LINENO 21
    
    PRINT 'HELLO WORLD'
END