SQL Server Performance

How to use raiserror in UDF's

Discussion in 'SQL Server 2005 CLR' started by sun_psna, Jan 2, 2008.

  1. sun_psna New Member

    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...
  2. satya Moderator

    RAISERROR statement cannot be used within a UDF. In fact, you can't even check the value of the @@ERROR global variable within a function. If you encounter an error, UDF execution simply stops, and the calling routine fails. You are allowed to write a message to the Windows error log with xp_logevent if you have permission to use this extended procedure.
    You could look at TRY..CATCH, http://technet.microsoft.com/en-us/library/ms175976.aspx fyi.
  3. sun_psna New Member

    Thanks Satya.
    But we need to migrate the existing Oracle function without any major changes. Is that possible to do using CLR functions? If not then we have the only option to convert this function into procedure. Am I Correct?
  4. satya Moderator

    Yes, I think CLR can help you then.

Share This Page