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]

Matt (ma********@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, es****@sommarskog.se

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