At my job we’ve recently upgraded from SQL Server 2016 to SQL Server 2019. After the upgrade, one group ran into issues with a user-defined function, returning different values for the same input. The function was similar to this sample function.

GO
CREATE OR ALTER FUNCTION dbo.fnDoesDatabaseExist
	(@DatabaseName nvarchar(30))
RETURNS bit
AS
BEGIN
	DECLARE @ReturnValue bit;
	SELECT @ReturnValue = 0;

	SELECT @ReturnValue = 1
	FROM sys.databases 
	WHERE [name] = @DatabaseName;

	RETURN @ReturnValue;
END

GO

We’ll run the function twice, one for a database that exists and again for a database that doesn’t exist, We’ll run both on SQL Server 2019 (Level 150) and again on Level 130 (for SQL Server 2016).

ALTER DATABASE TestDB SET COMPATIBILITY_LEVEL = 150;  
GO

SELECT dbo.fnDoesDatabaseExist('ZZZ');
SELECT dbo.fnDoesDatabaseExist('TestDB');

GO

ALTER DATABASE TestDB SET COMPATIBILITY_LEVEL = 130;  
GO

SELECT dbo.fnDoesDatabaseExist('ZZZ');
SELECT dbo.fnDoesDatabaseExist('TestDB');

GO

When running the function for a database that doesn’t exist, we get 0 on compatibility level 130, but a NULL on level 150.
It’s odd that running the same select outside of a function returns 0 on both compatibility levels.

DECLARE @ReturnValue bit;
SELECT @ReturnValue = 0;

SELECT @ReturnValue = 1
FROM sys.databases 
WHERE [name] = 'ZZZZ';

SELECT @ReturnValue;

It certainly pays to test any database code before migrating to a new version, sometimes a subtle change can break existing code.