All 7 Error Handling with TRY CATCH questions
More in Error Handling with TRY CATCH
How does a TRY CATCH block work in SQL Server, and why is it useful? How do you combine a TRY CATCH block with a transaction to ensure the database is properly rolled back if an error occurs partway through? What is the difference between using THROW and RAISERROR to raise a custom error in SQL Server? What types of errors cannot be caught by a TRY CATCH block in SQL Server? How would you build a centralized, reusable error logging procedure that captures detailed error information whenever it is called from within a CATCH block? How do nested TRY CATCH blocks work, and when would you use them in a complex stored procedure?
Advertisement
SQL Server › Error Handling with TRY CATCH

What error information functions are available inside a CATCH block to help you understand what went wrong?

Beginner

Answer

Inside a CATCH block, you can use functions like ERROR_MESSAGE to get the actual error text, ERROR_NUMBER for the specific error code, ERROR_LINE for the line number where the error occurred, and ERROR_SEVERITY and ERROR_STATE for additional technical details, all of which help you log or respond to the error appropriately.
BEGIN CATCH
  SELECT ERROR_NUMBER() AS ErrorNumber,
         ERROR_MESSAGE() AS ErrorMessage,
         ERROR_LINE() AS ErrorLine;
END CATCH;
Real-world example A logging system captures the specific error number, message, and line number whenever a stored procedure fails, giving the support team detailed information to quickly diagnose exactly what went wrong.

Common follow-ups: Can these error functions be used outside of a CATCH block?;What is the difference between error severity and error state?

Stored Procedures & Functions;SQL Server Profiler & Extended Events
All 7 Error Handling with TRY CATCH questions