How to use raiserror in UDF's

Question

How to use raiserror in UDF's

sun.psna

SSC-Addicted

Points: 410
More actions
January 2, 2008 at 5:23 am

#181719

Im working in a Oracle to SQL migration project, I need to migrate a function
which is using Raiserror()
I have a function in Oracle like this,
create function fn_name( parameters )
returns int
as begin
if ( condition )
-- do some logic
else
raiseerror()
end
I need to migrate this to SQL server 2005.
From next version we wont have Extended procedure, so its better to avoid.
Instead that we can use CLR integration.
Can anyone help me out...

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Andras Belokosztolszki SSC-Insane Points: 21971 More actions · Answer 1

You cannot use raiserror in a user defined function written in T-SQL. You could rewrite your function as a stored procedure with an output parameter for the return value, but whether you can do this depends on how you were using your function originally.

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

sun.psna SSC-Addicted Points: 410 More actions · Answer 2

Thanks Andras...

Since i don't have any idea about the application & how the functions were called from the application. I'll check with my client and update you soon.

By any chance, Do you have any idea like - if the function used in Select/Case statement. then what will be the solution?

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 3

No, no... no need to convert to a proc... when your code detects that something is wrong, just do a SELECT 1/0 and, trust me, an error will be raised 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sun.psna SSC-Addicted Points: 410 More actions · Answer 4

Thanks Jeff Moden.

yes, you are correct, Select 1/0 will raise the error (Msg 8134, Level 16, State 1 - Divide by zero error encountered.)

But is that possible to change the error message as per our wish?

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 5

Sadly, no... no such luck.

About the only thing you could to is have the function return a special value and check that value in the calling code... if your code determines that it's an error value, then you could raise a "controlled" error.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ddriskell SSC Rookie Points: 49 More actions · Answer 6

Why not return a -1 or some other value that would flag it as an error?

Don

sun.psna SSC-Addicted Points: 410 More actions · Answer 7

Instead of returning the value and raising error, I took the previous option i.e. converting into Stored procedures.

This took only less time for me to convert.

Thank you Guys, For your support...:)

EvilDev SSC Rookie Points: 43 More actions · Answer 8

I realize this is an old post, but it comes up on google pretty high when you ask how to "throw error in UDF" so lemme answer with a trick I use to accomplish this for the next person that might find a need for this.

My trick, is to force an invalid cast exception (by trying to convert a VARCHAR to an INT for example). In the same spirit as "SELECT 1/0".

The side effect, is the string value your trying to convert to an INT will get spit out to the message window, thus allowing you to send information when it happens.

CREATE FUNCTION ThrowError

RETURNS INT

AS

BEGIN

DECLARE @result INT

SELECT @result = 'We have a major problem here' -- This throws a conversion error

RETURN 1

END

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 9

And from someone brand-spanking-new to the forum, no less. Nice trick, Mitch. Thanks for posting it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)