ERROR_LINE()返回1而不是SQL动态查询执行中的确切行号。

huangapple
go评论109阅读模式
英文:

Why ERROR_LINE() returns 1 instead of exact line number in sql dynamic query execution

问题

我在我的过程中执行了许多动态查询，所以如果这些查询中发生了任何错误，我想知道在哪一行没有发生错误，我总是得到错误的行号，即1，这是不正确的。

我使用了 ERROR_LINE() 但它在动态 SQL 查询中不起作用。

  1. DECLARE @LineNumber INT,@QUERY VARCHAR(MAX)
  3. BEGIN TRY
  4. SET @QUERY='SELECT 1/0'
  5. EXEC (@QUERY);
  6. THROW 50000, 'Line#', 1;
  7. END TRY
  8. BEGIN CATCH
  9. SET @LineNumber = ERROR_LINE();
  10. END CATCH
  11. SELECT @LineNumber;

在这里，我想知道哪一行没有发生错误。正确的行号是5，但SQL Server 总是显示1，这是错误的。

提前谢谢。

英文:

I executes numbers of dynamic queries in my procedure so if any error occurred in those query I want to know on which line no error occurred, I always get wrong line no which is 1, which is incorrect.

I used ERROR_LINE() but it does not work with a dynamic SQL query.

  1. DECLARE @LineNumber INT,@QUERY VARCHAR(MAX)
  3. BEGIN TRY
  4. SET @QUERY='SELECT 1/0'
  5. EXEC (@QUERY)
  6. ;THROW 50000, 'Line#', 1
  7. END TRY
  8. BEGIN CATCH
  9.     SET @LineNumber = ERROR_LINE()
  10. END CATCH
  11. SELECT @LineNumber

Here I want to know on which line no error occurred. The correct line no is 5 but SQL Server always shows 1 which is wrong.

Thanks in advance.

答案1

得分: 1

这是有点“hacky”的方法，但它确实提供了正确的行号。我将你提供的代码放入一个 TRY...CATCH 中，如果遇到错误，我会将错误编号和消息传递回调用语句。然后，我检查 @ErrorNumber 的值是否不是 NULL，如果不是，就在下一行引发一个通用错误。然后我知道外部批处理中的错误发生在 ERROR_LINE()-1。有点不太好，但确实有效：

  1. DECLARE @LineNumber int,
  2.         @SQL nvarchar(MAX),
  3.         @CRLF nchar(2) = NCHAR(13) + NCHAR(10),
  4.         @ErrorNumber int,
  5.         @ErrorSeverity int,
  6.         @ErrorState int,
  7.         @ErrorLine bigint,
  8.         @ErrorMessage nvarchar(4000);
  9.         
  11. BEGIN TRY
  12.     SET @SQL = N'BEGIN TRY' + @CRLF +
  13.                N'    SELECT 1/0;' + @CRLF +
  14.                N'END TRY' + @CRLF +
  15.                N'BEGIN CATCH' + @CRLF +
  16.                N'    SET @ErrorNumber = ERROR_NUMBER();' + @CRLF +
  17.                N'    SET @ErrorSeverity = ERROR_SEVERITY();' + @CRLF +
  18.                N'    SET @ErrorState = ERROR_STATE();' + @CRLF +
  19.                N'    SET @ErrorLine = ERROR_LINE();' + @CRLF +
  20.                N'    SET @ErrorMessage = ERROR_MESSAGE();' + @CRLF +
  21.                N'END CATCH;';
  22.     EXEC sys.sp_executesql @SQL, N'@ErrorNumber int OUTPUT, @ErrorSeverity int OUTPUT, @ErrorState int OUTPUT, @ErrorLine bigint OUTPUT, @ErrorMessage nvarchar(4000) OUTPUT', @ErrorNumber OUTPUT, @ErrorSeverity OUTPUT, @ErrorState OUTPUT, @ErrorLine OUTPUT, @ErrorMessage OUTPUT;
  23.     IF @ErrorNumber IS NOT NULL THROW 100000,'error',16;
  24. END TRY
  25. BEGIN CATCH
  26.     IF @ErrorNumber IS NULL
  27.         THROW;
  28.     SET @LineNumber = ERROR_LINE()-1;
  29.     --SELECT @LineNumber, @ErrorMessage;
  30.     SET @ErrorNumber = 100000 + @ErrorNumber;
  31.     SET @ErrorMessage = CONCAT(N'An error was encountered in a dynamic batch executed on Line ', @LineNumber, N'.',@CRLF, N'    ', N'Msg ', @ErrorNumber-100000, N', Level ', @ErrorSeverity, N', State ', @ErrorState, N', Line ', @ErrorLine, @CRLF, N'    ', @ErrorMessage);
  32.     THROW @ErrorNumber, @ErrorMessage, @ErrorSeverity;
  33. END CATCH;

请注意，这不适用于不可“CATCH”的错误，例如引用不存在的对象。

英文:

This is a little "hacky" but it does provide the correct line number. I dump the code you have into a TRY...CATCH and then if an error is encountered I instead pass the error number and message back to the calling statement. Then I check if the value of @ErrorNumber isn't NULL and THROW a generic error on the next line. I then know that the error in the outer batch occured on the ERROR_LINE()-1. Yuck, but it does work:

  1. DECLARE @LineNumber int,
  2.         @SQL nvarchar(MAX),
  3.         @CRLF nchar(2) = NCHAR(13) + NCHAR(10),
  4.         @ErrorNumber int,
  5.         @ErrorSeverity int,
  6.         @ErrorState int,
  7.         @ErrorLine bigint,
  8.         @ErrorMessage nvarchar(4000);
  9.         
  11. BEGIN TRY
  12.     SET @SQL = N'BEGIN TRY' + @CRLF +
  13.                N'    SELECT 1/0;' + @CRLF +
  14.                N'END TRY' + @CRLF +
  15.                N'BEGIN CATCH' + @CRLF +
  16.                N'    SET @ErrorNumber = ERROR_NUMBER();' + @CRLF +
  17.                N'    SET @ErrorSeverity = ERROR_SEVERITY();' + @CRLF +
  18.                N'    SET @ErrorState = ERROR_STATE();' + @CRLF +
  19.                N'    SET @ErrorLine = ERROR_LINE();' + @CRLF +
  20.                N'    SET @ErrorMessage = ERROR_MESSAGE();' + @CRLF +
  21.                N'END CATCH;';
  22.     EXEC sys.sp_executesql @SQL, N'@ErrorNumber int OUTPUT, @ErrorSeverity int OUTPUT, @ErrorState int OUTPUT, @ErrorLine bigint OUTPUT, @ErrorMessage nvarchar(4000) OUTPUT', @ErrorNumber OUTPUT, @ErrorSeverity OUTPUT, @ErrorState OUTPUT, @ErrorLine OUTPUT, @ErrorMessage OUTPUT;
  23.     IF @ErrorNumber IS NOT NULL THROW 100000,'error',16;
  24. END TRY
  25. BEGIN CATCH
  26.     IF @ErrorNumber IS NULL
  27.         THROW;
  28.     SET @LineNumber = ERROR_LINE()-1;
  29.     --SELECT @LineNumber, @ErrorMessage;
  30.     SET @ErrorNumber = 100000 + @ErrorNumber;
  31. SET @ErrorMessage = CONCAT(N'An error was encountered in a dynamic batch executed on Line ', @LineNumber, N'.',@CRLF, N'    ', N'Msg ', @ErrorNumber-100000, N', Level ', @ErrorSeverity, N', State ', @ErrorState, N', Line ', @ErrorLine, @CRLF, N'    ', @ErrorMessage);
  32.     THROW @ErrorNumber, @ErrorMessage, @ErrorSeverity;
  33. END CATCH;

Note that this will not work on errors that aren't "CATCHable", such as references to objects that don't exist.

huangapple
  • 本文由 发表于 2023年6月19日 20:49:45
  • 转载请务必保留本文链接：https://go.coder-hub.com/76506807.html
  • dynamic-sql
  • exception
  • sp-executesql
  • sql-server
  • try-catch
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定