An important part of building CLR objects for use in SQL Server is good exception management. Part of this is raising the exception back to the calling operation so that it can be handled appropriately. In .NET this is handled using try/catch blocks in the code, and throwing a new instance of the Exception class with a message. A simple example of this can be seen in the following CLR Stored Procedure:
}
Calling this procedure with a non-integer input variable will result in an Exception being raised back to the caller. In TSQL you can also use a try/catch block to handle this execption as follows:
begin try
exec GenerateError 'This will fail'
end try
begin catch
print error_message()
end catch
This will result in an error being printed out like the following:
A .NET Framework error occurred during execution of user-defined routine or aggregate "GenerateError":
System.Exception: The input value "This will fail" could not be converted to an Int32!
---> System.FormatException: Input string was not in a correct format.
System.FormatException:
at System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal)
at System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo info)
at System.Convert.ToInt32(String value)
at StoredProcedures.GenerateError(String input)
System.Exception:
at StoredProcedures.GenerateError(String input)
.
The second line of this contains the actual error message that was raised from inside of the CLR Procedure. The rest of it , is detailed Exception information from inside of the .NET Exception, to include the inner exception data that this was a System.FormatException specifically. However, if you don't care about all of this, and only want the original error message that was raised in your code, you can create a function to eliminate all of the .NET exception data:
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString CleanCLRError(SqlString errorMsg)
{
return new SqlString(errorMsg.ToString().Split("\r\n".ToCharArray(), StringSplitOptions.RemoveEmptyEntries)[1]);
}
While this can be done in CLR, it can also be done inside TSQL as well, and since it is a function being called in both cases, and the function is deterministic, the TSQL Implementation is the most appropriate method for implementation. The code for the TSQL implementation would be:
create function dbo.CleanCLRError_TSQL(@errorMsg varchar(max))
returns varchar(max)
as
begin
return (left(substring(@errorMsg, patindex('%'+char(10)+'%', @errorMsg), len(@errorMsg)), patindex('%'+char(10)+'%', substring(@errorMsg, patindex('%'+char(10)+'%', @errorMsg)+1, len(@errorMsg)))))
end
This will result in the same output as the CLR function when called:
declare @err varchar(max)
begin try
exec GenerateError 'This will fail'
end try
begin catch
print dbo.CleanCLRError(error_message())
print dbo.CleanCLRError_TSQL(error_message())
end catch
The resulting error is as follows:
System.Exception: The input value "This will fail" could not be converted to an Int32!