Seeing errors in callers, SQLCLR error 6522, and T-SQL TRY-CATCH

I've been going over how errors are raised in SQLCLR against the SQL Server 2005 RTM version. If you remember (search the blog on "6522" if you don't) how errors made their way from the managed to the unmanaged stack evolved throughout the beta. My goal in investigating this was to determine if you can make SQLCLR acts the same way that T-SQL acted with respect to errors. Then, you could replace T-SQL functions and procedures with SQLCLR without changing every caller.

6522 (that's a general error that means "an error in the SQLCLR stack occurred) is still with us. You get one if there is an unhandled exception in ExecuteNonQuery or ExecuteReader inside a SQLCLR proc.If you want this error to go away and want only the original SQL error (e.g. 547 referential integrity error) to be returned to the caller, the only way to do this is to use SqlPipe.ExecuteAndSend in your SQLCLR procs. If you have no catch block, both the 6522 and the "original" (e.g. 547) error will be returned. If you're called from client code, the error number is e.g. 547, the 6522 follows afterward. If you want to "lose" the 6522 altogether use ExecuteAndSend and use a dummy try-catch block in your SQLCLR code. It would look like this:

try {
…
…
SqlContext.Pipe.ExecuteAndSend(cmd)
}
catch { // no code here
}

In this case you only get the original error e.g. the 547. Note that if you use SqlCommand's ExecuteNonQuery or ExecuteReader with the dummy try-catch concept, you lose the error entirely.

Bear in mind that if you use T-SQL's TRY-CATCH in SQL Server 2005, there is no way to raise *exactly* the original error either. You can come close, but the is no RETHROW. You can use RAISERROR, but RAISERROR doesn't work with system errors. You can reformat the original error message in a user error.

Upshot of this is that if you convert to either SQLCLR or T-SQL TRY-CATCH and depend on seeing the original error number at the caller (normally the case even if you have error handling in your procs), you're going to be changing the callers' code. Or using only ExecuteAndSend.