I am using a clr procedure in one of my normal stored procs and i'm experiencing an odd error handling situation. Check the following code, it works as expected...
BEGIN TRY
create table #test(id varchar(2))
insert #test
select '123'
select * from #test
END TRY
BEGIN CATCH
select 'an error occured but was handled'
END CATCH
However, if I use a clr stored proc that returns a value too large, say '123':
BEGIN TRY
create table #test(id varchar(2))
insert #test
EXEC my_clr_proc
select * from #test
END TRY
BEGIN CATCH
select 'an error occured but was handled'
END CATCH
..I get the following 'String or binary data would be truncated' as well as my error in the catch statement. So the original error is being thrown even though i'm saying i'll handle it.
It seems there is something different about the way it is dealing with the error just because the data is returned from a clr procedure. I'm not sure why and I need to know the best way to handle this.
thanks.