How to use raiserror in UDF's

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » CLR Integration and Programming. » How to use raiserror in UDF's

How to use raiserror in UDF's

Rate Topic

Display Mode

Topic Options

Author

Message

sun.psna

Posted Wednesday, January 02, 2008 5:23 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, July 24, 2008 6:38 AM
Points: 4, Visits: 17

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...

Post #437924

Andras Belokosztolszki

Posted Wednesday, January 02, 2008 5:48 AM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, January 26, 2012 5:26 AM
Points: 1,367, Visits: 1,585

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

Post #437931

sun.psna

Posted Wednesday, January 02, 2008 6:01 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, July 24, 2008 6:38 AM
Points: 4, Visits: 17

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?

Post #437935

Jeff Moden

Posted Wednesday, January 02, 2008 6:47 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 4:51 PM
Points: 32,923, Visits: 26,811

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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #437950

sun.psna

Posted Wednesday, January 02, 2008 7:20 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, July 24, 2008 6:38 AM
Points: 4, Visits: 17

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?

Post #437971

Jeff Moden

Posted Wednesday, January 02, 2008 9:26 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 4:51 PM
Points: 32,923, Visits: 26,811

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

Post #438228

ddriskell

Posted Thursday, January 03, 2008 6:25 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, January 11, 2008 5:59 AM
Points: 3, Visits: 12

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

Don

Post #438347

sun.psna

Posted Friday, January 11, 2008 2:43 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, July 24, 2008 6:38 AM
Points: 4, Visits: 17

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...:)

Post #441635

mitch 81999

Posted Monday, December 17, 2012 12:57 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, December 17, 2012 1:00 PM
Points: 1, Visits: 2

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

Post #1397394

Jeff Moden

Posted Monday, December 17, 2012 6:16 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 4:51 PM
Points: 32,923, Visits: 26,811

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

--Jeff Moden

Post #1397491

« Prev Topic | Next Topic »

Permissions