Art Of Programming

musings by Dmytrii Nagirniak

Overcoming Unavailable RAISERROR in UDF

If you want to indicate an error from UDF first thing you will try (SQL Server) is:
RAISERROR('Invalid argument for MyUDF', 16, 1)

BUT, this won’t work and you will get an error:
Invalid use of 'RAISEERROR' within a function.

This is disappointing (also mention the misspelled RAISEERROR in the message).
Searching the can bring alternatives.
The simplest one is division by zero:
SELECT 1/0
With this people will remember you for a long time (and not from a good side). PLEASE DO NOT DO IT FOR GOD’S SAKE.

I choose the one below. It at least allows me to include the error message:
SET @dummy = (SELECT CAST(('Invalid argument for MyUDF') AS INT))
And will give error message:
Syntax error converting the varchar value 'Invalid argument for MyUDF' to a column of data type int.

Better than division by zero and definitely better than nothing.
Hope this helps.

Comments