Blog: How to Raise an Exception in SQL Server User Defined Functions

There are a lot of differences between Oracle and MS SQL Server and you will face many of them trying to move your database from one platform to another. If you use SSMA, which we highly recommend, you can avoid countless hours of manual work, but you will have to solve some specific problems by yourself after the SSMA job has finished. One of them can be raising errors from UDF, which you can easily do in Oracle and can’t in SQL Server. Let’s discover the workarounds to raise an exception in SQL Server User Defined Functions because of T-SQL limitations for UDF.

Exception in SQL Server User Defined Functions

Converting Oracle UDF to SQL Server

So let’s create some pretty simple UDF in Oracle and take a look on how we can keep its full functionality in MS SQL.


IF (b=0) THEN
RAISE_APPLICATION_ERROR(-20002, 'You cannot divide by zero!');


If you try to rewrite it in T-SQL just with obvious syntax and data types changes like that:

RETURNS float(53)
IF (@b = 0)
RAISERROR(59998, 16, 1, 'You cannot divide by zero!')
RETURN CAST(@a AS float(53)) / @b

you will get an error:

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

Let’s see what SSMA does to avoid this (and many other) T-SQL restrictions for UDF. If you are new to SSMA you can start with this presentation by our CEO Dmitry Balin, which describes the main idea of migration process. It also should be enough to make first steps with SSMA.

Possible workarounds

So, first of all SSMA creates “sysdb” database with collection of auxiliary objects (like tables, SPs, UDFs etc). Part of them is just emulating Oracle build-in functionality and the other helps to deal with more complicated situations.

Raising errors from UDF

You can’t raise errors from UDF, but you can do that from a stored procedure, so SSMA creates one:

@a int,
@b int,
* SSMA warning messages:
* O2SS0356: Conversion from NUMBER datatype can cause data loss.

@return_value_argument float(53) OUTPUT
/*Generated by SQL Server Migration Assistant for Oracle version 5.2.1259.*/

IF (@b = 0)

@db_raise_application_error_message nvarchar(4000)

SET @db_raise_application_error_message = N'ORA' + CAST(-20002 AS nvarchar) + N': ' + N'You cannot divide by zero!'

RAISERROR(59998, 16, 1, @db_raise_application_error_message)


SET @return_value_argument = CAST(@a AS float(53)) / @b



It contains all the logic from the original UDF and next problem here is that you can’t invoke stored procedure from UDF. But you can execute an extended stored procedure and call regular stored procedure from it. “Sysdb” includes “xp_ora2ms_exec2_ex” extended stored procedure which is just a wrapper for calling regular stored procedures from UDF (you can read about this and other migration troubleshooting in SSMA White Paper).

Emulating Oracle functionality in SQL Server

So, the whole thing here is that we create stored procedure where the logic is implemented, wrap it with extended stored procedure and call the last from UDF. It looks like obvious overhead, causes performance issues and… doesn’t work. You can’t receive exception from extended SP and if you run the following query

select [dbo].[DIVIDE] (1,0)

it will return null.

This trick with extended procedure is good when you need to emulate other Oracle functionality which is forbidden in T-SQL, but it doesn’t work if you just need to raise error from UDF. So what is the solution?

The most known trick here is to force UDF fail with system exception:

ALTER FUNCTION DIVIDE (@a int, @b int)
RETURNS float(53)
IF (@b = 0)
return cast('You cannot divide by zero!' as int)
RETURN CAST(@a AS float(53)) / @b

It looks much less complicated than previous solution and it really works! Now if you run

select [dbo].[DIVIDE] (1,0)

again you will get a result message like below:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value ‘You cannot divide by zero!’ to data type int.

Using linked servers

Well, it doesn’t look perfect, but it’s definitely better than nothing. But what if for some reasons you have to specify error severity and state? There is another much more refined way to accomplish that. It’s similar to the first solution but now we will use OPENQUERY instead of extended procedure.

First of all, let’s create loopback linked server:

EXEC sp_addlinkedserver @server = N'loopback',
@srvproduct = N' ',
@provider = N'SQLNCLI',
@datasrc = N'Your server name',
@catalog = N'master'

After that we need a stored procedure (a difference from the first solution you have to create only one SP for raising errors from all your UDFs):

create proc raise_error
@errorstr varchar(max),
@errorsvr int = 11,
@errorstate int = 1
set nocount on
select null as result;

And finally here is our “divide” function:

ALTER FUNCTION DIVIDE (@a int, @b int)
RETURNS float(53)
declare @fake_for_error int
IF (@b = 0)
select @fake_for_error = 0 from openquery(loopback, 'EXEC [Your Database Name].dbo.raise_error @errorstr = ''You cannot divide by zero'', @errorsvr = 18, @errorstate = 3')
RETURN CAST(@a AS float(53)) / @b

And if you run select again, you will get clear error message with custom severity and state values:

Msg 50000, Level 11, State 3, Line 1
You cannot divide by zero

But what about performance? Well, calling stored procedure through the linked server is a little overhead and if performance is critical you should use “cast message to int” trick instead. But in most cases query time will increase almost insensibly, so if you want to get a clean error message with custom state and severity levels you can use the loopback linked server version.