NULL and empty string. Don't forget nothing.

Just geeking with nothing. 

We have NULLs and empty strings in TSQL, but I should explore nothing more than I have. What's nothing you say?  I'm not sure, it's sort of tough to describe.  Run the following 

SELECT   id
FROM sys.sysobjects
WHERE id = 999999

What did you get?  Nothing. 

This came up today because I was working quickly and not thinking and happened to throw something similar to this into a query window: 

DECLARE @ID int

SELECT @ID = ISNULL(id,9999)

FROM sys.sysobjects

WHERE id = 999999

SELECT @ID

You'd think that it might give you the the familiar Error 141 Severity 15 that we've all gotten once or twice in our lifetime..."A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations." It doesn't. How about this one?

IF (SELECT   id

FROM sys.sysobjects

WHERE id = 999999

) =

(SELECT   id

FROM sys.sysobjects

WHERE id = 999999

)

       PRINT 1

ELSE

       PRINT 2

Nice!  Well, at least we know that there is some consistency about.  As NULL cannot equal another NULL (unless ANSI settings are adjusted), we can say with a degree of certainty that nothing cannot equal nothing.  Maybe I'll write an article on nothing as I did on NULLs someday.

Naaaa.

Lee Everest

------------------------------------

Wasn't that a Billy Preston song a hundred years ago?