Post reply

Error Handling in UDFs

  • March 26, 2008 at 9:01 pm

    #184558

    Hi All

    I did some googling and after referring to article http://qa.sqlservercentral.com/articles/News/exceptionhandlinginsqlserver2005/2237/

    If its not possible to use TRY CATCH kind of error handling in UDFs, then how most of the DBA's set error handling standards?

    I did some testing and was able to use @@ERROR in UDFs but with @@ERROR i have to handle all the errors every SQL query. Is there any better way to get around this and use some sort of TRY CATCH in UDFs.

    I found TRY CATCH better than @@ERROR. The reason i am stressing on TRY CATCH is that i am writing a document stating SQL standards and error handling is part of it.

    Any help is appreciated.

    Anam

  • March 26, 2008 at 9:05 pm

    #794427

    I dont think you can use try catch. You will have to take the tedious road for udfs.

    This link may help you.

    http://www.sommarskog.se/error-handling-I.html


    My blog: http://jahaines.blogspot.com

  • March 26, 2008 at 10:57 pm

    #794442

    Hi Adam

    The link you provided is really good.

    But unfortunately i am back in the grave. The test i did with @@ERROR also failed. It compiles but fails when i execute the function, it doesnt capture the error.

    the function i created is below

    ALTER FUNCTION dbo.testfnc()

    RETURNS datetime

    BEGIN

    declare @tmpuser datetime

    SELECT @tmpuser = convert(datetime, '2003123')

    -- i am passing getdate as test only to check if control goes to IF statement

    IF @@error <> 0

    SELECT @tmpuser = getdate()

    RETURN @tmpuser

    END

    SELECT dbo.testfnc()

    The error message i got after execution is expected one as i am using wrong value.

    Msg 241, Level 16, State 1, Line 1

    Conversion failed when converting datetime from character string.

    Please help me out in handling errors in UDFs.

Viewing 3 posts - 1 through 2 (of 2 total)

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