Naveen covered the TSQL exception handling in his posts on sqlprogrammability blog site. How does the TSQL exception handling mechanism interacts with SQLCLR exception handling mechanism? We will cover this topic in this post.
 
When SQL server execute a user function/procedure/trigger implemented in CLR (i.e., managed code), we will install a managed exception handler around the user code. So if the user code leaked a exception, the server will catch it and throw a TSQL exception wrapping the user exception. Here is an example:
 
Msg 6522, Level 16, State 1, Procedure UDP_OpenFile, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'UDP_OpenFile':
System.IO.FileNotFoundException: Could not find file 'C:\nonexisiting'.
System.IO.FileNotFoundException:
   at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
   at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURI 
Now, what happens if your CLR function/procedure calls back into SQL server again through InProc data access and caused a TSQL exception? For example, if a stored procedure is implemented in CLR like the following:
 
public static void AddNewBook()
{
 using(SqlConnection cnn = new SqlConnection(someConnectionString))
 {
  conn.Open();
  using(SqlCommand cmd = conn.CreateCommand())
  {
   cmd.CommandText = "insert t_Books (title, author) values('title', 'author')";
   cmd.ExecuteNonQuery();
  }
 }
}

What if the INSERT statement failed due to a duplicated key violation? SQL server will translate such normal TSQL exceptions into a CLR SqlException object. When this happens, the TSQL exception is considered as been handled. The system no longer has any pending TSQL exceptions at all, instead a managed SqlException will be thrown. Your code will see a SqlException. You can catch it through your CLR exception handler. This mechanism allows to catch TSQL exceptions in your CLR function/procedure.

However, if your CLR code doesn't catch the SqlException, SQL server will see that you leaked an unhandled CLR exception. Currently, the infrastructure doesn't treat SqlException differently from any other CLR exception (e.g. FileNotFoundException), so we will give an error 6522 with the exception message and stack.
 
Simply speaking, normal TSQL exceptions doesn't pass through CLR frames on the stack. It will get translated into appropriate SqlException. This SqlException can be handled by user code. If not, SQL server will treat it the same way as any other unhandled CLR exception.
 
I used the world "normal" TSQL exceptions in the above explaination, which suggests that there are "unusal" TSQL exceptions that behaves differently. That is indeed the case. Those are server TSQL exceptions that will either stop the execution of the batch or terminate the connection. Such TSQL exceptions are server enough to warrant special treatment. They will be translated into System.Threading.ThreadAbortException. Since ThreadAbortException can't be handled (well, it can be caught, but it will be rethrow immediately after catch block unless you explicitly reset it, which will be considered as bug in user code), the CLR frame will unwind and when the control goes back to TSQL, the original server TSQL exception will be thrown.
 
If this sounds a little complicated, you are right. Exception handling is tricky. We are looking into ways to make the mechanism more intuitive and friendly. Your experience anc comments about the current mechanism will help us to understand how to make things easier for you in the future.
 
[Cross Posted from http://blogs.msdn.com/sqlprogrammability]