Show command executed when SQL error occurs

Question

Considering the following command executed

BEGIN TRY  
    -- Generate a divide-by-zero error.  
    SELECT 1/0;  
END TRY  
BEGIN CATCH  
    SELECT  
        ERROR_NUMBER() AS ErrorNumber  
        ,ERROR_SEVERITY() AS ErrorSeverity  
        ,ERROR_STATE() AS ErrorState  
        ,ERROR_PROCEDURE() AS ErrorProcedure  
        ,ERROR_MESSAGE() AS ErrorMessage;  

END CATCH;  
GO 

The answer to it will be:

ErrorNumber
8134

ErrorSeverity
16

ErrorState
1

ErrorProcedure
NULL

ErrorMessage
Divide by zero error encountered.

Is there any way to get which execution block was executed to generate this exception?

I wish the answer was:

Command

'SELECT 1/0;'

Answer 1

Jhonathan, it’s usually not a conventional error exit, but this bit can help you:

BEGIN TRY  
    -- Generate a divide-by-zero error.  

    SELECT 1 / 0;
END TRY
BEGIN CATCH
    SELECT [qt].text
      FROM [sys].[dm_exec_connections] AS [A]
           CROSS APPLY [sys].[dm_exec_sql_text]([A].[most_recent_sql_handle]) AS [qt]
     WHERE [session_id] = @@SPID;
END CATCH;  
GO

But in my opinion, you could collect these errors otherwise, in an audit.

Sqlserver: Query error audit

Answer 2

A solution and you create a variable to store the description of the block being executed and concatenate in the message.

declare @BlocoExe varchar(max)

BEGIN TRY  
    -- Generate a divide-by-zero error. 
    set @BlocoExe  = 'Bloco exe: SELECT 1/0 - Erro:'; -- descrição do bloco
    SELECT 1/0;  
END TRY  
BEGIN CATCH  
    SELECT  
        ERROR_NUMBER() AS ErrorNumber  
        ,ERROR_SEVERITY() AS ErrorSeverity  
        ,ERROR_STATE() AS ErrorState  
        ,ERROR_PROCEDURE() AS ErrorProcedure  
        , @BlocoExe + ERROR_MESSAGE()  AS ErrorMessage ;  

END CATCH;  
GO