How can I get the actual stored procedure line num

问题:

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.

TIA!

回答1:

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.

回答2:

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.

回答3:

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.

回答4:

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

回答5:

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())

回答6:

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())

回答7:

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..

回答8:

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