ISNULL - 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

SQL Server Development (2000)

ISNULL

Author

Topic

TAS
Yak Posting Veteran

65 Posts

Posted - 2005-09-23 : 10:22:17

I have program to select data from address table.If city is entered, The program will display all data under that city. Otherwise, if city is not entered, I want to display all cities' data under that state. I tried ISNULL function and CASE statement,it seems both not working.

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-09-23 : 10:26:30

select city from state where state = @state or @state is null

Duane.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-23 : 10:27:57

Try this

Select Columns from yourTable where city=@city or city is Null

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2005-09-23 : 11:18:49

Or even:


SELECT Col1, Col2, ... 
FROM yourTable 
WHERE city=@city OR (city IS NULL AND state = @state)

Kristen

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-09-23 : 11:42:54

Or even:

SELECT Col1, Col2, ...
FROM yourTable
WHERE city=@city OR (city IS NULL AND state = @state)
and zip like '[0-9][0-9][0-9][0-9][0-9][0-9]'

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

Kristen
Test

22859 Posts

Posted - 2005-09-23 : 14:06:05

Yeah, or even anything, but ZIP wasn't in the question Xerxes - you gotta go back to your skool days where the teacher said "READ THE QUESTION" - at least that's what they used to say over here!