How to get hardcoded date form table

  • i have a table called Config with columns id and value.

    id holds data called 1 and value holds data called hardcoded date '11/6/2010'

    i have a function where it returns datetime

    (@Sa  varchar(30),
     @type varchar(30))
    returns datetime
    as
    BEGIN
     declare @ret datetime

    SELECT @ret = CASE WHEN MAX(Date) IS NULL
       THEN '11/6/2010'
       ELSE  MAX(Date)
        END
       from dbo.EmpDate where Sa= @Sa  and type = @type  
     return @ret

    END

    Now instead of directly hardcoding the date in the function, I want to join with config table to get the hardcoded date.

  • SELECT @ret = CASE WHEN MAX(Date) IS NULL 
    THEN c.Value
    ELSE MAX(Date) 
    END
    from dbo.EmpDate e
    JOIN Config c ON c.id = 1
    where e.Sa= @Sa and e.type = @type 
    return @ret

    Be careful with scalar-valued functions, though: they're performance killers.  If you use them in a query, the value is calculated individually for each row.  Consider converting to an inline table-valued function instead.

    John

  • John Mitchell-245523 - Monday, January 23, 2017 8:15 AM

    SELECT @ret = CASE WHEN MAX(Date) IS NULL 
    THEN c.Value
    ELSE MAX(Date) 
    END
    from dbo.EmpDate e
    JOIN Config c ON c.id = 1
    where e.Sa= @Sa and e.type = @type 
    return @ret

    Be careful with scalar-valued functions, though: they're performance killers.  If you use them in a query, the value is calculated individually for each row.  Consider converting to an inline table-valued function instead.

    John

    How to convert this as an Table Valued Fucntion?

  • This should help you.

    John

  • Also, to reduce errors and potential errors, don't code dates in an ambiguous format, i.e.,
    does '11/6/2010' represent Nov 6 or Jun 11??
    or waste space with formatting chars.  

    Instead, use YYYYMMDD format, like so:

    '20101106'

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

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

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