To NULL or not to NULL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

To NULL or not to NULL

Author

Topic

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2003-07-31 : 10:25:59

I'm sure this is very simple but I can't work it out - yes, another thick day!

In a select statement I want to return TRUE or FALSE according to whether a single field is NULL or not.

I want something like

IF Field1 is null then true
ELSE false

but I can't use is null and ISNULL doesn't seem to let me do it either. The only way I can seem to get it to work is using a CASE statement which seems very clunky.

anyone have any suggestions? What am I missing?

thanks

steve

Steve no function beer well without

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-07-31 : 10:51:10

You can use

IF @VAR IS NULL THEN ....


DECLARE @VAR INT

SET @VAR = NULL

IF @VAR IS NULL  BEGIN PRINT 'TRUE' END

SET @VAR = 10

IF @VAR IS NULL  BEGIN PRINT 'TRUE' END ELSE BEGIN PRINT 'FALSE' END

don't be afraid to use the CASE, that's what it's there for!

----------------
Shadow to Light

SamC
White Water Yakist

3467 Posts

Posted - 2003-07-31 : 10:51:33

HOWSABOUT

SELECT CASE WHEN Field1 IS NULL THEN TRUE ELSE FALSE END AS MyBooleanColumn

FROM MyTable

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-07-31 : 11:33:02

But what are TRUE and FALSE?
(That's not a philosophical question)

SamC
White Water Yakist

3467 Posts

Posted - 2003-07-31 : 11:44:36

Do you mean "Do we really know if anything is really true?"

If you're a basic VBscript programmer

SELECT CASE WHEN Field1 IS NULL THEN 'TRUE' ELSE 'FALSE' END AS MyBooleanColumn

FROM MyTable

or if you have a math mind:

SELECT CASE WHEN Field1 IS NULL THEN 1 ELSE 0 END AS MyBooleanColumn

FROM MyTable

or maybe you're as comfortable with logical expressions as others are with math:

SELECT CASE WHEN Field1 IS NULL THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END AS MyBooleanColumn

FROM MyTable

If you are a philosopher:

SELECT CASE WHEN Field1 IS NULL THEN 'What can be known?' ELSE 'Everything is futile.' END AS MyBooleanColumn

FROM MyTable

My error anyway (if anyone can really be wrong)

HTH,

Sam

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2003-08-03 : 17:32:21

In answer to the 'not a philosophical question', it doesn't really matter apart from some values possibly being slower to work with. I just need two different values that I can assign a meaning to. The current favourite with the users is 'TRUE' and 'FALSE'!! Not ideal in my opinion but who am I in all this (and that is a philosophical question)

steve

Steve no function beer well without

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources