Error Notification from User Defined Function

[Matt]

Is there any reason why I shouldn't cause an arithmetic error(say by
dividing by zero) in a User Defined Function for a situation where in a
stored procedure you would use RAISERROR or in code you would throw an
exception?

In most situations, I can check the return value of the UDF to see that
it is valid. However, I want to sum the output of UDF and therefore
cannot check the return value. Errors should be so rare that I would
rather not use a cursor to do the sum--and take the peformance hit.

Following is the code:

SELECT SUM([dbo].[udf_MyFunction]([Column1],[Column2]))
FROM tblMyTable

Thanks ~ Matt

[Erland Sommarskog]

Re: Error Notification from User Defined Function

Matt (mattmorg55@gma il.com) writes:[color=blue]
> Is there any reason why I shouldn't cause an arithmetic error(say by
> dividing by zero) in a User Defined Function for a situation where in a
> stored procedure you would use RAISERROR or in code you would throw an
> exception?
>
> In most situations, I can check the return value of the UDF to see that
> it is valid. However, I want to sum the output of UDF and therefore
> cannot check the return value. Errors should be so rare that I would
> rather not use a cursor to do the sum--and take the peformance hit.[/color]

Trapping errors from UDF is not any simple affair. The normal procedure
would be:

SELECT SUM([dbo].[udf_MyFunction]([Column1],[Column2]))
FROM tblMyTable
SELECT @err = @@error

But unfortunately, when you get an error in a UDF, @@error is not
set for the caller. (This has been addressed in SQL 2005, which also
has a vastly improved error handling.)




--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp