Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to use raiserror in UDF's Expand / Collapse
Author
Message
Posted Wednesday, January 2, 2008 5:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Wednesday, January 2, 2008 5:48 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen 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
Posted Wednesday, January 2, 2008 6:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Wednesday, January 2, 2008 6:47 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:13 PM
Points: 36,995, Visits: 31,514
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #437950
Posted Wednesday, January 2, 2008 7:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Wednesday, January 2, 2008 9:26 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:13 PM
Points: 36,995, Visits: 31,514
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #438228
Posted Thursday, January 3, 2008 6:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Friday, January 11, 2008 2:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Monday, December 17, 2012 12:57 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Monday, December 17, 2012 6:16 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:13 PM
Points: 36,995, Visits: 31,514
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1397491
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse