Function Returning NULL on Specific Server Instance, 0 on Other Servers

  • This has me very perplexed... we have a function that on one server is returning a 0 but on another server it returns a NULL:

    USE TempDB 
    GO

    CREATE FUNCTION dbo.TestNuLL()
    RETURNS BIT
    AS
    BEGIN
    DECLARE @MyBit BIT;
    SET @MyBit = 0

    SELECT @MyBit = 1 WHERE 1 = 0

    RETURN @MyBit
    END

    I tested this on a number of different servers and only one appears to be bringing back a NULL instead of a 0.

    I ran this, just in case:

    IF ( (32 & @@OPTIONS) = 32 ) 
    PRINT 'The ANSI_NULLS option turned on.'
    ELSE
    PRINT 'The ANSI_NULLS option turned off.';

    And all query windows are producing the same message, i.e., "The ANSI_NULLS option turned on".

    Gotta admit, this has me stumped!

    Please.... no comments about the use if ISNULL() or COALESCE().... this is trying to figure out WHY one SQL 2019 server is doing this while all of the other servers are producing a different result.

    Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)

  • Somehow you left out the "SET @MyBit = 0" on one function?

    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".

  • Somehow you left out the "SET @MyBit = 0" on one function?

    No... all the functions come from a central location.

    Strange part, if I script out the function and just execute the code, I get a "0", not a NULL. Only when I run it as a function does it return NULL.

    Also, changing the function to RETURN ISNULL(@MyBit,'0') works, but that does not explain HOW/WHY it is happening.

    • This reply was modified 8 months, 3 weeks ago by  DB_Newbie2007.
    • This reply was modified 8 months, 3 weeks ago by  DB_Newbie2007.
    • This reply was modified 8 months, 3 weeks ago by  DB_Newbie2007.

    Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)

  • I like, in general, ingimas, as I feel they help us learn and grow (and, for myself, the greater the challenge the more I dig my heals in to try and figure it out).

    It appears this is an issue with the SQL Server version (15.0.2101.7) and the handling of UDFs. We updated the version and the UDF works fine now (still working on an actual root cause).

    • This reply was modified 8 months, 3 weeks ago by  DB_Newbie2007.

    Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)

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

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