How to report an error from a SQL Server user-defined function

Question

I'm writing a user-defined function in SQL Server 2008. I know that functions cannot raise errors in the usual way - if you try to include the RAISERROR statement SQL returns:

Msg 443, Level 16, State 14, Procedure ..., Line ...
Invalid use of a side-effecting operator 'RAISERROR' within a function.

But the fact is, the function takes some input, which may be invalid and, if it is, there is no meaningful value the function can return. What do I do then?

I could, of course, return NULL, but it would be difficult for any developer using the function to troubleshoot this. I could also cause a division by zero or something like that - this would generate an error message, but a misleading one. Is there any way I can have my own error message reported somehow?

Answer 1

RAISEERROR or @@ERROR are not allowed in UDFs. Can you turn the UDF into a strored procedure?

From Erland Sommarskog's article Error Handling in SQL Server – a Background:

User-defined functions are usually invoked as part of a SET, SELECT, INSERT, UPDATE or DELETE statement. What I have found is that if an error appears in a multi-statement table-valued function or in a scalar function, the execution of the function is aborted immediately, and so is the statement the function is part of. Execution continues on the next line, unless the error aborted the batch. In either case, @@error is 0. Thus, there is no way to detect that an error occurred in a function from T-SQL.

The problem does not appear with inline table-functions, since an inline table-valued function is basically a macro that the query processor pastes into the query.

You can also execute scalar functions with the EXEC statement. In this case, execution continues if an error occurs (unless it is a batch-aborting error). @@error is set, and you can check the value of @@error within the function. It can be problematic to communicate the error to the caller though.

Answer 2

One way (a hack) is to have a function/stored procedure that performs an invalid action. For example, the following pseudo SQL

create procedure throw_error ( in err_msg varchar(255))
begin
insert into tbl_throw_error (id, msg) values (null, err_msg);
insert into tbl_throw_error (id, msg) values (null, err_msg);
end;

Where on the table tbl_throw_error, there is a unique constraint on the column err_msg. A side-effect of this (at least on MySQL), is that the value of err_msg is used as the description of the exception when it gets back up into the application level exception object.

I don't know if you can do something similar with SQL Server, but worth a shot.

Answer 3

The usual trick is to force a divide by 0. This will raise an error and interrupt the current statement that is evaluating the function. If the developer or support person knows about this behavior, investigating and troubleshooting the problem is fairly easy as the division by 0 error is understood as a symptom of a different, unrelated problem.

As bad as this looks from any point of view, unfortunately the design of SQL functions at the moment allows no better choice. Using RAISERROR should absolutely be allowed in functions.

Answer 4

I think the cleanest way is to just accept that the function can return NULL if invalid arguments are passed. As long is this is clearly documented then this should be okay?

-- =============================================
-- Author: AM
-- Create date: 03/02/2010
-- Description: Returns the appropriate exchange rate
-- based on the input parameters.
-- If the rate cannot be found, returns NULL
-- (RAISEERROR can't be used in UDFs)
-- =============================================
ALTER FUNCTION [dbo].[GetExchangeRate] 
(
    @CurrencyFrom char(3),
    @CurrencyTo char(3),
    @OnDate date
)
RETURNS decimal(18,4)
AS
BEGIN

  DECLARE @ClosingRate as decimal(18,4)

    SELECT TOP 1
        @ClosingRate=ClosingRate
    FROM
        [FactCurrencyRate]
    WHERE
        FromCurrencyCode=@CurrencyFrom AND
        ToCurrencyCode=@CurrencyTo AND
        DateID=dbo.DateToIntegerKey(@OnDate)

    RETURN @ClosingRate 

END
GO