none
Exception Handling in SQL Functions

    Question

  •  

    can I handle exception in SQL functions? how??
    I am tring to migrate the Database from Oracle 9i to SQL server 2005 with SSMA which converts function in one procedure and wrapper function because of exception handling in Oracle.
     
     
    Tuesday, May 20, 2008 7:47 AM

All replies

  • in sql server 2005, the TRY…CATCH construct cannot be used within a user-defined function.

     

    Madhu

     

    Tuesday, May 20, 2008 7:55 AM
    Moderator
  • Shouldn't the exception handling be done in that procedure then?

    What kind of exceptions?   (there are some limitation to Try-Catch in TSQL)

    What about @@ERROR ?


    Tuesday, May 20, 2008 8:35 AM
  •  

    When I Migrate the Oracle Database to SQL Server 2005 Database it migrates the functions as well but while migrating the function like

     

    FUNCTION AV_TYPE(full_desc VARCHAR2) RETURN VARCHAR2 IS

    short_desc VARCHAR2(30);

    BEGIN

    SELECT DESCRIPTION INTO short_desc

    FROM M3.REFERENCE_CODES WHERE RD_CODE = 'Voting Preference'

    AND RC_CODE = full_desc;

    RETURN short_desc;

    EXCEPTION

    WHEN NO_DATA_FOUND THEN

    short_desc := 'no data found';

    RETURN short_desc;

    WHEN OTHERS THEN

    -- Consider logging the error and then re-raise

    RAISE;

    END AV_TYPE;

     

    which is very simple one it creates two objects as

                           Implementation of a function in the form of a procedure

                           A function that is a wrapper for the procedure calling

    this is because in this function the Exception is handled

     

    So I would like to rewrite the functions (instead of migrating them with the tool) with the same functionality can i do that?

    Tuesday, May 20, 2008 9:27 AM
  •  

    SSMA Converts functions with Two objects if the function conatins Exception handling:

                           Implementation of a function in the form of a procedure

                           A function that is a wrapper for the procedure calling

     

    so the simplest function in oracle become the complex two different objects

    e.g.,  the oracle functions

     

    FUNCTION AV_TYPE(full_desc VARCHAR2) RETURN VARCHAR2 IS

    short_desc VARCHAR2(30);

    BEGIN

    SELECT DESCRIPTION INTO short_desc

    FROM M3.REFERENCE_CODES WHERE RD_CODE = 'Voting Preference'

    AND RC_CODE = full_desc;

    RETURN short_desc;

    EXCEPTION

    WHEN NO_DATA_FOUND THEN

    short_desc := 'no data found';

    RETURN short_desc;

    WHEN OTHERS THEN

    -- Consider logging the error and then re-raise

    RAISE;

    END AV_TYPE;

     

     

    get converted to two Objects onr Procedure and one Wrapper function can i conver this function in SQL w/o any procedure with the same functionality?

    Tuesday, May 20, 2008 9:31 AM
  • You must not return SELECT results from scalar functions in SQL Server. Rewrite your code using a stored procedure instead. This will also give you the opportunity of using TRY/CATCH.

    Tuesday, May 20, 2008 9:45 AM
  • Ya, that's true. It's not good practice. I think this should be done by join.

    Anyway, if you still need to do it through function, code like this should work.

    Code Snippet

    CREATE FUNCTION dbo.AV_TYPE(@full_desc nvarchar) 
    RETURNS nvarchar(30)
    AS
    BEGIN
        DECLARE @RETVAL nvarchar(30)
        SET @RETVAL = (SELECT DESCRIPTION INTO short_desc FROM M3.REFERENCE_CODES WHERE RD_CODE = 'Voting Preference'AND RC_CODE = @full_desc)
        IF @RETVAL is null
            BEGIN
            -- Consider logging here
            SET @RETVAL = 'No data found'
            END
        RETURN @RETVAL
    END;



    Test it...
    Code Snippet

    select dbo.AV_TYPE('test description')


    If condition just tests for your No_data_Found exception. If you suspect on other exceptions, then it should be taken care by more if's...

    Regards,
    Tuesday, May 20, 2008 9:56 AM
  • hmm this makes sense i will ry doing that

     

    Tuesday, May 20, 2008 10:09 AM
  •  

    but what in case of other errors??

    eg., for the statements like

     

    WHEN OTHERS THEN

    -- Consider logging the error and then re-raise

    RAISE;

    Tuesday, May 20, 2008 10:19 AM
  • What kind of errors?

    You can add something like,
    Code Snippet

        IF @@ERROR <> 0 --Refer books online.
            set @RETVAL = 'Error'


    But i doubt if its of any use.

    Tuesday, May 20, 2008 10:34 AM
  •  PreetiMuley wrote:

     

    but what in case of other errors??

    eg., for the statements like

     

    WHEN OTHERS THEN

    -- Consider logging the error and then re-raise

    RAISE;

     

    In sql server RAISERROR you can not use within a function. You better write this logic in a SP (if possible) coz, you may have performance propblem when you use Scalar function in a select query and its a known fact in sql server (i am not sure about oracle)

     

    Madhu

    Tuesday, May 20, 2008 10:36 AM
    Moderator
  •  Madhu K Nair wrote:
     PreetiMuley wrote:

     

    but what in case of other errors??

    eg., for the statements like

     

    WHEN OTHERS THEN

    -- Consider logging the error and then re-raise

    RAISE;

     

    In sql server RAISERROR you can not use within a function. You better write this logic in a SP (if possible) coz, you may have performance propblem when you use Scalar function in a select query and its a known fact in sql server (i am not sure about oracle)

     

    Madhu

     

    Actually i am migrating the Database and currently the application is already developed for oracle DB so would not prefer changing the function into SP and ne ways I cannot use SP in my Select statement to return a scalar value. 

    Tuesday, May 20, 2008 11:06 AM