Custom Sql Exceptions through the CLR

Friends,

First off, congrats and thank you to everyone at Microsoft for all of the hard work they have put into Sql Server 2005 and .NET 2.0 - it is simply amazing technology.

On that note, I was wondering if it was possible to create my own custom exceptions that I can throw in my stored procedures and then catch in my application code?

For example, say I wanted to create a Custom Sql Exception called "DuplicateEmailInSqlDatabaseTableException" and then, within a stored procedure where data is being attempted to be inserted, I could check for a duplicate email record and then throw the exception. At that point, I would like to be able to catch that exception in my C# data layer and work from there.

Is this possible? I feel like it could be but am unsure where to start.

Shaun C McDonnell

Is there anyone out there? ;)|||You can definitely throw a custom exception. The problem however is that this will then propagate through from CLR -> SQL -> Client. When the exception leaves the CLR inside SQL Server it goes through the SQL layer. SQL sees that this is now an unhandled exception and wraps a 6522 error ("A .NET Framework error occurred during execution error around your exception ...") around your exception. So when the client receives the exception it is no longer your custom exception, but a SqlException.

You would have thought that your exception would be part of the SqlError collection as a specific error object, but it's not. You have to, in the Message property, look for your Exception class name together with the error message and retrieve it from there as a string.

Niels|||

We are considering optionally tunneling (serialize exception on the server & deserialize on the client) SqlClr exceptions to managed clients for future versions of SqlServer.

We are also looking into the possibility of exposing managed exceptions to TSQL (IE. @.@.ClrException) as UDT to allow TSQL inspection.

Beysim.