英文:
why @@error issue does not catch the error ID number?
问题
我尝试执行 SELECT 1/0 然后执行 SELECT @@ERROR 在 SQL 服务器中。然而，它没有捕获错误ID，就像它应该的那样。我知道 @@ERROR 存储该值直到成功执行TSQL查询。
为什么会这样？
英文:
I tried executing SELECT 1/0 and then executing SELECT @@ERROR in SQL server. However, it does not catch the error ID as it's supposed to. I know that @@ERROR stores the value until a successful TSQL query is executed.
Why is this happening?
SELECT 1/0
SELECT @@ERROR
It should be giving answer like this:
8134
But it Returns:
0
Video link to for @@ERROR not working --
https://clipchamp.com/watch/Isn7KyXv549
Video link to @@ERROR working as expected --
https://clipchamp.com/watch/EqJ1cMrBwRS
答案1
得分: 4
我知道@@ERROR会存储该值，直到成功执行TSQL查询。
这不是@@ERROR重置的唯一原因。它还与批处理相关。您将这些命令作为两个单独的批处理运行。
事实上，当我在我的系统上同时运行这两个命令时，我会得到两个结果集。第一个是空的（失败了），第二个包含值8134。
英文:
> I know that @@ERROR stores the value until a successful TSQL query is executed.
That's not the only reason @@ERROR resets. It's also specific to a batch. You are running these commands as two separate batches.
And in fact, when I run the two commands together on my system, I get two result sets. The first is empty (it failed), and the second has the value 8134.
答案2
得分: 3
我能够在SSMS 18中重现视频1的行为，以及在SSMS 19中重现视频2的行为。我添加了一个分析器来检查这里发生了什么。
一般来说 - 如果你突出显示单行并按F5键，SSMS会将它们作为单独的批处理运行 - 所以，例如，如果你分别运行以下代码：
DECLARE @i INT = 0;
SET @i = @i + 1;
第二行将失败，并显示"必须声明标量变量@i"，因为每一行都是一个单独的批处理（不知道之前的运行情况）。
这也会发生在你分别运行以下行：
SELECT 1/0
SELECT @@ERROR
不同之处在于，@@ERROR是一个全局变量，可以独立于之前的任何批处理选取。
为什么会得到不同的结果？如果你附加分析器，你会看到SSMS 18运行以下语句：
SELECT 1/0
SELECT @@SPID --（不可见，不是由你触发的，而是由SSMS 18在后台触发的，从而将@ERROR重置为0）
SELECT @@ERROR
而SSMS 19实际上只执行以下内容：
SELECT 1/0
SELECT @@ERROR
（如果你足够快，并且在这两个语句之间没有其他代码在后台执行，例如通过任何作业执行）。
因此，你不能依赖于@@ERROR在两个手动运行批处理之间保持不变。只有如果你突出显示这两行并一起执行它们（按F5键）在一个单独的批处理中，它应该返回正确的@@ERROR代码。
英文:
I was able to reproduce this behavior of video 1 in SSMS 18 and of video 2 in SSMS 19. I added a profiler to check what happens here.
In general - if you highlight single lines and press f5 SSMS is running these as separate batches - so e. g. in case you run
DECLARE @i INT = 0;
SET @i = @i + 1;
separately, the second line will fail with "Must declare the scalar variable @i." - because each one is a separate batch (and doesn't know about the run before).
This happens also in case you run these lines seperately
SELECT 1/0
SELECT @@ERROR
The difference is here only, that @@ERROR is a global variable and can be selected independent from any batches before.
Why do you get a different result? If you attach the profiler you can see that SSMS 18 runs these statements:
SELECT 1/0
SELECT @@SPID -- (not visible and not triggered by you but by SSMS 18 in the background and thus resetting @ERROR to 0)
SELECT @@ERROR
while SSMS 19 really executes only
SELECT 1/0
SELECT @@ERROR
(in case you are fast enough and not other code is executed in the background by e. g. by any job between these 2 statements).
So, you cannot rely that @@ERROR will be kept between 2 manual run batches. Only if you highlight both lines and execute them together (press F5) in a single batch it should return the correct @@ERROR code.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论