none
how to handle error in scalar function?

    Question

  • Hi, I have the follow function:

    CREATE FUNCTION [dbo].[ToTime]
    (
     @intHora int, --A valid hour
     @intMin int -- A valid minute
    )
    RETURNS smalldatetime
    AS
    BEGIN
     declare @strTime smalldatetime
     declare @errorvar int
     
     select @strTime=cast(convert(varchar,cast((cast(@intHora as varchar) +':'+ cast(@intMin as varchar)) as  smalldatetime),108) as varchar)
     return @strTime;
    END

    the function works perfect but when the parameter for the hour is a negative number (for example -1), or a number > 23
    and the parameter for the minute is an negative number (-1) or a number > 59, the function  produce an error.
    I need handle this error converting the wrong value in 0, but i don't want to do this using "if statement". for example

     if @intHora < 0 or @intHora >23
     begin
       set @intHora = 0
     end
     if @intMin <0 or @intMin>59
     begin
       set @intMin = 0
     end

    please, If someone know some sql function (try - catch doesn't work) to handle this kind of error or some good way to do it, please help me.

    Wednesday, November 28, 2007 9:00 PM

All replies

  • You can use and IF block, or a CASE structure.

     

    Why would you NOT use an IF block to validate your inputs?

     

    Wednesday, November 28, 2007 9:11 PM
  •  

    Hi, I have the follow function:

     

    CREATE FUNCTION [dbo].[ToTime]
    (
           @intHora int, --A valid hour
           @intMin int -- A valid minute
    )
    RETURNS smalldatetime
    AS
    BEGIN
           declare @strTime smalldatetime
           declare @errorvar int
     
           select @strTime=cast(convert(varchar,cast((cast(@intHora as varchar) +':'+ cast(@intMin as varchar)) as smalldatetime),108) as varchar)
          return @strTime;

    END

     

    the function works perfect but when the parameter for the hour is a negative number (for example -1), or a number > 23
    and the parameter for the minute is an negative number (-1) or a number > 59, the function  produce an error.
    I need handle this error converting the wrong value in 0, but i don't want to do this using "if statement". for example:

     

     if @intHora < 0 or @intHora >23
     begin
       set @intHora = 0
     end
     if @intMin <0 or @intMin>59
     begin
       set @intMin = 0
     end

     

    please, If someone know some sql function (try - catch doesn't work) to handle this kind of error or some good way to do it, please help me.

    Wednesday, November 28, 2007 9:17 PM
  • diego jaramillo,

     

    You can not handle errors inside the udf. You have to handle it in the script using the udf.

     

    In this case, you could use function ISDATE for a quick check of the value.

    Code Block

     

    CREATE FUNCTION dbo.ufn_convert_to_datetime (

    @h INT,

    @m INT

    )

    RETURNS DATETIME

    AS

    BEGIN

     

    DECLARE @d DATETIME

     

    IF ISDATE(RIGHT('00' + LTRIM(@h), 2) + ':' + RIGHT('00' + LTRIM(@m), 2)) = 1

    SET @d = CONVERT(DATETIME, RIGHT('00' + LTRIM(@h), 2) + ':' + RIGHT('00' + LTRIM(@m), 2), 108)

    ELSE

    SET @d = 0

     

    RETURN @d

    END

    GO

     

    SELECT

    dbo.ufn_convert_to_datetime(26, 60),

    dbo.ufn_convert_to_datetime(23, 37)

    GO

     

    DROP FUNCTION dbo.[ufn_convert_to_datetime]

    GO

     

     

    AMB
    Wednesday, November 28, 2007 9:31 PM
  • Do you need to return a smalldatetime or can you return a varchar()?

     

    Adam

    Friday, November 30, 2007 5:07 PM