REPEATABLE READ

I'm still looking into this one myself. But at this point I'm thinking I may have stumbled on to a bug. Someone explain what's up with the following:

-- returns 0123456789
SELECT ISNULL(NULL,'0123456789')  

-- returns NULL
SELECT NULLIF('0123456789','0123456789') 

-- works, because 01234 is shorter than 0123456789
SELECT ISNULL(NULLIF('0123456789','0123456789'),'01234') 

-- doesn't work, because 0123456789 is longer than 0123, so it truncates
SELECT ISNULL(NULLIF('0123','0123'),'0123456789 is longer than 0123 so it truncates')		

I ran into this working on a query/sproc for aspadvice, and when I saw it thought something must've been goofy with my connection settings... so I fired the same query off against my local box, then against some other boxes... the behavior can be 100% duplicated anywhere this query is run (drop it into your Query Analyzer to see it in action).

Oh, and for those of you asking why I'd want to use such a goofy combo, it has some very COOL uses (provided the bug doesn't get in the way). It can be very helpful in SELECTs where no value is passed in for one of the arguments. (Keep use of this fairly small though, if you're looking for a good way to do dynamic queries without risking injection, check out this great post from David Penton.) For example:

CREATE PROC dbo.isNullNullIfTest
	@userID		int = -1,  -- bad example... but you'll get the point
	@startDate	dateTime,
	@endDate	dateTime

AS
	SET NOCOUNT ON
	
	-- grab back login counts grouped by days... 
	SELECT
		COUNT(users.loginID) [hits],
		DATEPART(dy,users.loginDate) [dayOfYear]
	FROM
		dbo.userLogins users
	WHERE
		users.loginDate BETWEEN @startDate AND @endDate
		-- if the @userID = -1, then this clause will be WHERE userID = userID
		-- 	which means that ALL userIDs will be pulled back, if it's not -1
		-- 	then just the userID specified will be pulled back... 
		AND userID = ISNULL(NULLIF(@userID,-1),userID)
	GROUP BY
		DATEPART(dy,users.loginDate)
	ORDER BY
		DATEPART(dy,users.loginDate)
RETURN 0
GO		

Anyhow... this is a trick I devised a while back (I'm sure other people have thought of it too)... so I'm sad to see it compromised (in contexts dealing with char/varchar) by what appears to be a bug. I'll be sure to post back if I find out what's up.