How to use raiserror in UDF's

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

  • 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

  • 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?

  • 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)

  • 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?

  • 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)

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

    Don

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

  • 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

  • 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)

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

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