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

Solution 1:

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.

Solution 2:

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.

Solution 3:

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)