Post reply

catching convert(,,) error in UDF

  • August 19, 2004 at 9:48 am

    #108584

    Hello. I'm trying to write a function which will convert some 4(integer) column date values and return a datetime type. If the date is null in my source, it is all zeros. Catching this easy but I would like to catch a potentially wrong value as well to make my function more reliable. Is there a way to trap an error on the convert function and have it return the minimum date on failure. My only other alternative is to write a more involved script to check the date values myself, which is possible, but I wanted to know if there is a way to trap the error raised by the Convert(,,) function.

    Here is my Stored Procedure. It's pretty straight-forward what I'm trying to accomplish, but the function will bomb out as soon as I use values that would overflow or underflow the convert function. 

    CREATE FUNCTION mdcyToDT(@m int, @d int , @c int ,@y int)
--m(month), d(day), c(Century), y (Year)
    RETURNS datetime
    AS
    BEGIN
 DECLARE @returnDate AS datetime, --return date
   @fullDate As int -- ISO standard Date
       SET @fullDate = (@c*1000000)+ (@y*10000) + (@m*100) + @d
   SET @returnDate = Convert(datetime,cast(@fullDate as char(8)), 112) --bombs here if bad date
     
   IF @@Error <> 0 
    BEGIN
     SET @returnDate = '01/01/1753'
    END
 RETURN @returnDate
END 

  • August 19, 2004 at 9:57 am

    #519842

    ISDATE ( expression )

     

    Use the above before attempting to convert.

    Dave Hilditch.

  • August 19, 2004 at 10:13 am

    #519848

    David.. Thanks that should work for me in this case.

     

    But is there a way to trap the error for perhaps another case? I know Yukon is supposed to have a try catch functionality. That's the effect I'm after.

  • August 19, 2004 at 10:48 am

    #519852

    I've not done this for a while but I believe if you stick what you want inside a stored procedure and handle the error from outside the stored procedure then you can simulate try/catch.  Don't quote me on this, but if memory serves me right then this should work.  May also work if you place the code you need to try inside a function call and place the catch (check @@ERROR) after the call to the function.

    Dave Hilditch.

    ps.  Can you let me know if this works so I can update my memory banks?

  • August 20, 2004 at 6:15 am

    #519972

    Hi Dave,

    Error trapping in UDF is very limited.  You're best approach is validating the input before use.  In this case, the ISDATE function would do the trick. 

    There's also the issue of what to do with invalid data?  Usually returning NULL is about the best you can do because you can't do RAISERROR in a UDF.

    I've written a chapter about error handling in UDFs in my book Transact-SQL User-Defined Functions.

    Regards,

    Andy

    Andrew Novick

    http://www.NovickSoftware.com

     

     

Viewing 5 posts - 1 through 4 (of 4 total)

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