Implementing Error Handling in User-Defined Functions

Implementing Error Handling in User -Defined Functions

• Implement error handling in user-defined functions.

Implementing error handling in user-defined functions is different from doing so with stored procedures or transactions because of the complication of side effects. You see, a RAISERROR statement isn't allowed inside a user-defined function because it has side effects, such as the possibility of logging the error into the Windows NT event log or the SQL Server error log. The only way to really return an error from a user-defined function is to have a specified, agreed-upon return code that means "The function didn't work." For a table-valued function, this could mean returning an empty table; for a scalar function, it could mean returning a zero, an empty string, or a NULL.

It's also not possible to detect errors the same way in a user-defined function because the @@ERROR variable isn't available. Remember back in Chapter 6 when you learned that the global variables (the variables that start with two at-signs) are actually system functions? Well, they're also non-deterministic system functions. @@ERROR is not guaranteed to return the same thing if you call it twice. So, there really isn't a good way to find out you've had an error, and there's not a good way to handle it even if you could find out that it happened .

The good news is that you can't really cause errors in a user-defined function that easily. Typically, the errors that you handle using the traditional @@ERROR and RAISERROR techniques are problems with constraint violations and out-of-bounds data going into a table. Because you can't really insert data into a permanent table or a temporary table, the only way you can create an error is to insert a bad value into a variable of type table . And if you do that, your stored procedure is going to stop executing, the batch or stored procedure that called it is going to abort, and you're off to the next statement.

Here's an example. Say that you have a function and a call like this:

 CREATE FUNCTION CauseError( @InsertValue int ) returns int as begin declare @LocalTable table ( Value int         ) insert into @LocalTable values (4 / @InsertValue) return 1 end go print master.dbo.CauseError(0) 

This is going to cause a divide-by-zero error: Divide by zero error encountered . The statement has been terminated . So, in the grand tradition of structured programming, you're going to need to handle the error before it arises, like this:

 CREATE FUNCTION CauseError2( @InsertValue int ) returns int as begin declare @LocalTable table ( Value int         )         IF @InsertValue = 0 Return 0 insert into @LocalTable values (4 / @InsertValue) return 1 end go print master.dbo.CauseError2(0) 

So, basically, the only way to handle errors in your code is to be aware of what values can cause errors to occur and to make sure the values are properly handled.