Easy query question - check for null or not null

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

SQL Server Development (2000)

Easy query question - check for null or not null

Author

Topic

groston
Yak Posting Veteran

61 Posts

Posted - 2005-03-09 : 13:08:22

In Table myTable, there is a column, myColumn. The only acceptable values for the data in myColumn are 'T' or 'F'. In a query, I want to be able to select either those rows for which
a) myColumn is 'T' or
b) for which myColumn is either 'T' or 'F'.

I did this in the following manner:

PROCEDURE dbo.myProc
@tfFlag char(1) -- either 'T' or 'F'
AS
SELECT *
FROM myTable
WHERE myColumn = 'T' OR myColumn = @tfFlag

Not exactly rocket science, but it gets the job done without having to resort to dynamic SQL.

I have since changed the manner in which I am using this table. Instead of myColumn having 'T' or 'F', it can have a string value or be null.

Question: How can an "equivalent" SP be written in this case? That is, I want to be able to select either those rows for which
a) myColumn is not null
b) for which myColumn is either null or not null.

Thank you.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-09 : 13:37:20

or myColumn is (not) null

Go with the flow & have fun! Else fight the flow

groston
Yak Posting Veteran

61 Posts

Posted - 2005-03-09 : 14:30:11

quote:
Originally posted by spirit1

or myColumn is (not) null

Yak Guru,

The magic parentheses look interesting, but I am not quite sure how to wrap this up into a neat query as I did with T/F. Would you mind being a little more explicit?

Thanks!

X002548
Not Just a Number

15586 Posts

Posted - 2005-03-09 : 14:34:04

post a sample ddl, sample code, and expected results..

I still don't understand your post

Brett

8-)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-09 : 14:50:56

a.)
SELECT *
FROM myTable
WHERE myColumn is not null
b.) -- here you select * rows as your condition states
SELECT *
FROM myTable

a+b.)
SELECT *
FROM myTable
WHERE (myColumn is not null and @tfFlag='1') or @tfFlag='0' --when @tfFlag='1' it means you want the 1st condition when 0 the 2nd

Go with the flow & have fun! Else fight the flow

groston
Yak Posting Veteran

61 Posts

Posted - 2005-03-09 : 15:09:43

Thanks for the help yakster! Nice solution.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources