Global exception handler in CLR Stored Procedure

I use C# and Sql Server 2008 and I have some try/catch blocks in my CLR Stored Procedure and these are caught. These are logged in a file and they return a code error and message using SqlPipe. These work fines. Now I need to do the same when I have some untrapped errors especially to have a trace in the log file!

I tried to use Application.ThreadException but this class is not available in the CLR Stored Proce开发者_StackOverflow中文版dure (WinForms). Do I have to use a naughty try/catch block in my entry Sql Procedure method or is there something better to do?

Thanks.

As I recall, SQL Server will dump unhandled exceptions to its own log file, in addition to returning them to the user. You might want to look into that.

The cleanest approach will be to use a try..catch block in the CLR method being invoked. Even better would be to keep the main functionality in a separate method that gets invoked by the CLR entry method:

[SqlProcedure]
public static void GetSomething(string value)
{
    try {
        DoGetSomething(value):
    }
    catch (Exception ex) {
        // error handling
    }
}

private static void DoGetSomething(string value)
{
    // implementation goes here
}