SQL 如何从错误消息中获取实际的存储过程行号？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4565614/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I get the actual stored procedure line number from an error message?
提问by chama
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。
回答by Rick
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，它从创建该过程的批处理开始开始计算行数。这意味着要么是脚本的开始，要么是 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）转到报告的错误行。
回答by Vorlic
Out of habit I place LINENO 0directly after BEGINin 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 0and bingo - you have the error's line number as represented in the query window.
出于习惯，我LINENO 0直接BEGIN放在我的存储过程之后。在这种情况下，这会将行号重置为零。然后只需将错误消息报告的行号添加到您编写LINENO 0和宾果游戏的SSMS 中的行号- 您有查询窗口中表示的错误行号。
回答by Edward
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 块并使用 RAISERROR() 进行 Try 块内的任何代码验证，则会报告错误行是 Catch 块所在的位置，而不是真正发生错误的位置。我像这样使用它来清除它。
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
回答by user2294834
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 as Int) as [Error_Number] - 您将获得所需的答案。
回答by jasttim
In TSQL / Stored Procedures
在 TSQL/存储过程中
You may get an error such as:
您可能会收到如下错误：
Msg 206, Level 16, State 2, Procedure myproc, Line 177 [Batch Start Line 7]
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
这意味着错误在批处理中的第 177 行。SQL 中不是 177。在我的情况下，您应该看到您的批处理开始于哪个行号 [7]，然后将该值添加到行号以找出错误的语句
回答by HAJJAJ
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())
回答by Andy Raddatz
The long answer: the line number is counted from the CREATE PROCEDUREstatement, plus any blank lines or comment lines you may have had above it when you actually ran the CREATEstatement, but not counting any lines before a GOstatement…
长答案：行号是从CREATE PROCEDURE语句中计算的，加上你实际运行CREATE语句时可能在它上面的任何空行或注释行，但不计算GO语句之前的任何行......
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 PROCEDUREand add some blank lines above the comments and above and below the first GOstatement to see the effect.
创建完成后，可以切换到ALTER PROCEDURE注释上方和第一条GO语句的上方和下方添加一些空行，以查看效果。
One very strange thing I noticed was that I had to run EXEC ErrorTestingin 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 ErrorTesting在一个新的查询窗口中运行，而不是在同一窗口的底部突出显示它并运行……当我这样做时，行号一直在增加！不知道为什么会这样..
回答by Badiparmagi
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())
