Home Forums SQL Server 2012 SQL Server 2012 - T-SQL Dynamic Return type in a function RE: Dynamic Return type in a function

  • March 5, 2015 at 4:11 pm

    #1782061

    Alan.B (3/5/2015)

    Chris, Jeff...

    If I understand the OP correctly, they could not solve this problem with ISNULL or COALESCE alone because of the awkward data type requirement.

    Let's review the requirement as the OP stated it...

    The code is working good. However i want the return type to be dynamic. If the data type supplied is integer then i want to return a integer value like 0 if its null. if the data value is varchar then i want to return 'No Data'

    Gosh, the folks writing the code should know if the column they're working with is an INT or a VARCHAR. It they don't there may be bigger problems at hand.

    If they know it's an INT, then ISNULL(INTcolumn,0) will work just fine.

    If they know it's a VARCHAR, then ISNULL(NULLIF(VCcolumn,''),'No Data') will work just fine and also handles blanks.

    And the fact that SQL_VARIANT returns a NULL for the datatype if it's passed a NULL is an even stronger reason to not try to do this by as a UDF.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)