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

Question

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

DB_Newbie2007

SSCarpal Tunnel

Points: 4448
More actions
February 9, 2024 at 8:51 pm

#4376121
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)

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

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

ScottPletcher SSC Guru Points: 101012 More actions · Answer 1

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

DB_Newbie2007 SSCarpal Tunnel Points: 4448 More actions · Answer 2

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 5 months, 2 weeks ago by DB_Newbie2007.
This reply was modified 5 months, 2 weeks ago by DB_Newbie2007.
This reply was modified 5 months, 2 weeks ago by DB_Newbie2007.

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

DB_Newbie2007 SSCarpal Tunnel Points: 4448 More actions · Answer 3

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 5 months, 2 weeks ago by DB_Newbie2007.

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