Error Notification from User Defined Function

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 ~ MattMatt (mattmorg55@.gmail.com) writes:
> 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.

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@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp