Speak guys!
2019's first post!
In this article, I will explain the difference between @@ ERROR and the ERROR_NUMBER () function, which in theory bring the same result, that is, the error number caused by some operation in the current session. The idea of writing about it came from a doubt of a Nigerian in the group SQL Server - DBAfrom Telegram and thought it would be nice to have this explanation posted on my blog to help others with the same question.
First, let's consult the official Microsoft documentation to see if we have any hints of the difference of these 2 methods:
Returns the error number of the last Transact-SQL statement executed. Returns 0 if the previous Transact-SQL statement did not find any errors.
Once @@ ERROR is erased and reset on each statement executed, refer to it immediately after the statement is checked or save it to a local variable that can be checked later.
This function returns the error number that caused the CATCH block of a TRY… CATCH construct to execute. When called on a CATCH block, ERROR_NUMBER returns the number of the error that caused the CATCH block to execute. ERROR_NUMBER returns NULL when called out of scope of a CATCH block.
ERROR_NUMBER returns a relevant error number, regardless of how many times or where it is executed within the scope of the CATCH block. It is different from a function like @@ ERROR, which returns only one error number in the statement immediately after the one that causes an error.
In a nested CATCH block, ERROR_NUMBER returns the error number specific to the scope of the CATCH block that referenced that CATCH block. For example, the CATCH block of an external TRY ... CATCH construct could have an internal TRY ... CATCH construct. Within this internal CATCH block, ERROR_NUMBER returns the error number that invoked the internal CATCH block. If ERROR_NUMBER is executed on the external CATCH block, it returns the error number that invoked that external CATCH block.
Looking at the documentation, it is clear that these 2 error code identification methods differ:
- @@ ERROR: Returns the error code of the last statement executed (with each new statement, the variable is reset). Returns 0 if it has no error or if the variable is zeroed. It is recommended to assign the @@ ERROR result to a local variable at the very beginning of CATCH, because ANY COMMAND causes the @@ ERROR to be reset (even a SELECT @@ ERROR).
- ERROR_NUMBER: Returns the error code within the scope of a CATCH block. Returns NULL if you try to use the command outside a CATCH block.
And now, let's see this working in practice!
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 |
BEGIN TRY SELECT 1/0 END TRY BEGIN CATCH -- Os 2 comandos vão trazer o mesmo retorno SELECT ERROR_NUMBER() AS [ERROR_NUMBER_1], @@ERROR AS [@@ERROR_1] BEGIN TRY EXEC('SELECT * FROM dbo.Teste_123') END TRY BEGIN CATCH /* Aqui, vou colocar um comando PRINT para "atrapalhar" o comportamento do @@ERROR Como o @@ERROR é resetado a cada comando executado, o PRINT vai fazer com que a variável @@ERROR seja zerada */ PRINT 'Esse print vai atrapalhar o @@ERROR' SELECT ERROR_NUMBER() AS [ERROR_NUMBER_2], @@ERROR AS [@@ERROR_2] BEGIN TRY EXEC dbo.stpTeste END TRY BEGIN CATCH -- Aqui nada de novo também.. os 2 terão o mesmo retorno SELECT ERROR_NUMBER() AS [ERROR_NUMBER_3], @@ERROR AS [@@ERROR_3] -- Mas se eu tentar executar novamente o comando... o ERROR_NUMBER permanece igual, enquanto o @@ERROR já foi zerado SELECT ERROR_NUMBER() AS [ERROR_NUMBER_3_DENOVO], @@ERROR AS [@@ERROR_3_DENOVO] END CATCH END CATCH /* Já aqui, o ERROR_NUMBER vai retornar a mensagem de erro do bloco 1, que é o escopo atual do bloco CATCH Já a variável @@ERROR ficará vazia, pois outros comandos foram executados depois da última mensagem de erro */ SELECT ERROR_NUMBER() AS [ERROR_NUMBER_VOLTOU_PRO_1], @@ERROR AS [@@ERROR_ZERADO_PORQUE_OUTRO_COMANDO_JA_FOI_EXECUTADO] END CATCH -- Por fim, como o ERROR_NUMBER() não está dentro de nenhum bloco de CATCH com erro, retornará NULL e a @@ERROR retornará 0 SELECT ERROR_NUMBER() AS [ERROR_NUMBER_FINAL], @@ERROR AS [@@ERROR_FINAL] |
Result:
But what if I am not using TRY..CATCH and want to capture the error code? Can I use either method?
Answer: NO! If not within a TRY .. CATCH block, the ERROR_NUMBER () function will return NULL, while the @@ ERROR variable will return the error code (remember @@ ERROR's conditions of use to prevent it from being reset and you miss this information).
And that's it folks!
Post very short and objective and I hope it helped clarify the difference between @@ ERROR and ERROR_NUMBER ().
And remember: USE TRY ... CATCH!
A good 2019 for you and until the next article!
Big hug!
