Error handling with THROW

The TRY... CATCH error handling construct is very similar in T-SQL to exception handling in languages such as C# or VB.NET. In T-SQL it consists of a TRY block and a CATCH block, which must always be paired. If an error occurs in the TRY block, it is passed to the CATCH block code to handle.

In previous versions of SQL Server you would use @@RAISE_ERROR and would need to neatly collect the error data and return this. Let us look at an example that will throw n error:

BEGIN TRY  
  DECLARE @MyInt int
  SET @MyInt = 1 / 0  
END TRY   
BEGIN CATCH  
  DECLARE @ErrorMessage nvarchar(4000), @ErrorSeverity int
  SELECT @ErrorMessage = ERROR_MESSAGE(), 
  @ErrorSeverity = ERROR_SEVERITY()  
  RAISERROR (@ErrorMessage, @ErrorSeverity, 1)  
END CATCH

This is the result:

In the preceding CATCH block, there is a lot of work going on to collect the details of this error. Now there is a slicker way of finding out what has caused the error. Compare the above code to the code below...