none
how to create select ... case with user defined function? RRS feed

  • Question

  • i have a select statment using case logic.  The user define function portion won't work.  

    following is an example of it.  how do make it work?

     

    -- works.
    select
      case
        when DATEDIFF(DD, GETDATE(), getdate())=0
      then -1
      end
    from myTableOne

    -- works
    select * from udf_myFunc('myInput')

    -- doesn't work.

    select
      case
        when DATEDIFF(DD,  udf_myFunc('myInput'), getdate())=0
      then -1
      end
    from myTableOne

    Msg 195, Level 15, State 10, Line 3

    'udf_myFunc' is not a recognized built-in function name.

    the user-defined function is
    create function dbo.udf_myFunc(@myInput varchar(50))
    returns table
    as
    return(
      select  max(start_date) as start_date 
      from myTableOne         ( nolock) 
      where  
      loan_Num=@loan_Num 
    )


    -- IBM has jobs for USA employee to work oversea (India) and get their wages.
    Monday, December 27, 2010 7:39 PM

Answers

  • udf_myFunc can return more than one value is table function in your sql query you use DATEIFF (DD,GETDATE(),getgate()) - GETDATE returns one value probably here is your problem

    create function udf_MyFunc (
    	@MyInput nvarchar(50) )
    RETURNS DATETIME
    BEGIN
    	DECLARE @Result datetime=(select max(start_date) as start_date 
     from myTableOne     ( nolock) 
     where  
     loan_Num=@loan_Num )
     RETURN @Result
    END
    
    and where is declaration of @loan_Num?

    • Marked as answer by light_wt Tuesday, December 28, 2010 6:07 AM
    Monday, December 27, 2010 7:48 PM

All replies

  • Well, that is because the DATEDIFF function expects a DATETIME, as second parameter, while you provide something that returns a table.

     

    Monday, December 27, 2010 7:46 PM
  • udf_myFunc can return more than one value is table function in your sql query you use DATEIFF (DD,GETDATE(),getgate()) - GETDATE returns one value probably here is your problem

    create function udf_MyFunc (
    	@MyInput nvarchar(50) )
    RETURNS DATETIME
    BEGIN
    	DECLARE @Result datetime=(select max(start_date) as start_date 
     from myTableOne     ( nolock) 
     where  
     loan_Num=@loan_Num )
     RETURN @Result
    END
    
    and where is declaration of @loan_Num?

    • Marked as answer by light_wt Tuesday, December 28, 2010 6:07 AM
    Monday, December 27, 2010 7:48 PM
  • Your function is an inline, table valued function. You should have a scalar function instead...

    Change the definition

    returns table

    to

    RETURNS datetime

    CREATE FUNCTION dbo.getWhen(@myInput varchar(50))
    RETURNS datetime
    AS
    BEGIN
    RETURN 
    (
     SELECT
      max(start_date) as start_date 
     FROM myTableOne ( nolock) 
     WHERE
      loan_Num = @myImput 
    )
    END
    
    

    Well, I should really put some here... *smiles
    Monday, December 27, 2010 7:49 PM
  • thanks, everyone.  you all've got the right answer and thanks to the detailed answer from Mattias Linda and TheCrash.  they are the best.

     

     


    -- IBM has jobs for USA employee to work oversea (India) and get their wages.
    Tuesday, December 28, 2010 6:09 AM