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 6:10 pm

    #1782071

    Jeff Moden (3/5/2015)

    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.

    Agreed.

    This is a bazaar requirement.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001