Hey guys!
First post of 2019!

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 number of errors caused by some operation in the current session. The idea of ​​writing about this came from a question from a Nigerian in the group SQL Server – DBA, from Telegram and I thought it would be cool to have this explanation published on my blog to help other people with the same question.

First, let's consult Microsoft's official documentation to see if we have any hints about the difference between these 2 methods:

@@ERROR

Returns the error number of the last Transact-SQL statement executed. Returns 0 if the previous Transact-SQL statement did not encounter any errors.

Since @@ERROR is cleared and reset on each executed statement, query it immediately after the statement is checked or save it to a local variable that can be checked later.

ERROR_NUMBER

This function returns the error number that caused the CATCH block of a TRY…CATCH construct to be executed. When called in a CATCH block, ERROR_NUMBER returns the number of the error that caused the CATCH block to be executed. ERROR_NUMBER returns NULL when called outside the 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. This is different from a function like @@ERROR, which only returns an error number in the statement immediately following the one that causes an error.

In a nested CATCH block, ERROR_NUMBER returns the number of the CATCH block scope-specific error that referenced this CATCH block. For example, the CATCH block of an external TRY…CATCH construct could have an internal TRY…CATCH construct. Within this inner CATCH block, ERROR_NUMBER returns the number of the error that invoked the inner CATCH block. If ERROR_NUMBER is executed in the outer CATCH block, it returns the number of the error that invoked that outer CATCH block.

Analyzing the documentation, it is clear the difference between these 2 methods of identifying the error code:

  • @@ERROR: Returns the error code of the last executed instruction (with each new instruction, the variable is reset to zero). Returns 0 if there is no error or if the variable is zero. It is recommended to assign the @@ERROR result to a local variable right at the beginning of the CATCH, as 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 of a CATCH block

And now, let's see this working in practice!

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'm not using TRY..CATCH and I want to capture the error code? Can I use either of the two methods?

Answer: NO! Se no is inside a TRY..CATCH block, the ERROR_NUMBER() function will return NULL, while the variable @@ERROR will return the error code (remember the conditions of use of @@ERROR to prevent it from being reset and you losing this information).

And that's it, folks!
Very short and objective post and I hope it helped clarify the difference between @@ERROR and ERROR_NUMBER().
And remember: USE TRY…CATCH!

Have a good 2019 and see you in the next article!
Big hug!