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

Evgeny_20336 0
Viewed 76269

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?

user-defined-functions sql-server-2008 tsql sql-server sql
9 Answers

You can use CAST to throw meaningful error:

create function dbo.throwError()
returns nvarchar(max)
as
begin
    return cast('Error happened here.' as int);
end

Then Sql Server will show some help information:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'Error happened here.' to data type int.
user_5744510

Following on from Vladimir Korolev's answer, the idiom to conditionally throw an error is

CREATE FUNCTION [dbo].[Throw]
(
    @error NVARCHAR(MAX)
)
RETURNS BIT
AS
BEGIN
    RETURN CAST(@error AS INT)
END
GO

DECLARE @error NVARCHAR(MAX)
DECLARE @bit BIT

IF `error condition` SET @error = 'My Error'
ELSE SET @error = '0'

SET @bit = [dbo].[Throw](@error)
user_3233160

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
user_115380
Related