如何从错误消息中获取实际存储过程行号?

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?

当我使用Sql Server时，出现了一个错误，错误消息给出了一个与存储过程中的行号没有关联的行号。我认为区别是由于空格和注释，但是真的吗?

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.

我使用的是sql server 2005。

TIA!

蒂娅!

7 个解决方案

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

IIRC，它开始从创建proc的批次开始计数行，这意味着要么是脚本的开始，要么是create/alter proc语句之前的最后一个“GO”语句。

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

更容易看出这一点的方法是提取SQL Server在创建对象时使用的实际文本。将输出切换到文本模式(使用默认键映射CTRL-T)并运行

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.

将结果复制到脚本窗口中以获得语法突出显示等等，然后使用goto行函数(我认为是CTRL-G)到报告的错误行。

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

出于习惯，我将LINENO 0直接放在存储过程中。在这种情况下，这会将行号重置为0。然后只需将错误消息报告的行号添加到SSMS中的行号中，在那里您编写了LINENO 0和bingo—您有在查询窗口中表示的错误行号。

#3

Actually this Error_number() works very well.

实际上，这个Error_number()非常有效。

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.

这个函数从最后一个GO(批分隔符)语句开始计数，因此如果您没有使用任何GO空格，并且它仍然显示错误的行号——然后添加7，就像在第7行中的存储过程中那样，批分隔符将自动使用。因此，如果您使用select Cast(Error_Number()+7作为Int)作为[Error_Number] -您将得到所需的答案。

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

如果您使用Catch块并在Try块中使用RAISERROR()进行任何代码验证，那么错误行将报告捕获块所在的位置，而不是发生真正错误的位置。我用这个来清理。

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:

在catch块中可以得到错误消息和错误行如下:

'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…

答案很长:行号是从CREATE PROCEDURE语句中计数的，加上实际运行CREATE语句时可能在上面有的任何空行或注释行，但不包括GO语句之前的任何行……

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

我发现，制作一个存储的proc来进行验证要容易得多:

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.

在创建了它之后，您可以切换它来更改过程，并在注释上面和上面和下面添加一些空行，并在第一个GO语句下面看到效果。

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

我注意到的一件非常奇怪的事情是，我必须在一个新的查询窗口中运行EXEC error test，而不是在同一个窗口的底部突出显示它并运行…不知道为什么会这样。

#1