Where Case statement - 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 2005 Forums

Transact-SQL (2005)

Where Case statement

Author

Topic

chrispy
Posting Yak Master

107 Posts

Posted - 2007-06-30 : 19:30:19

I am working on a longer SP where I built a temp table.

The temp table contains the columns:
LocID int,
State char(2),
ZoneName char (10)

I also have two variables passed to the SP. They are:
@State char (2),
@ZoneName char (10)

Both variables are coming from a dropdown (or select box) on a web page and if one equals ='all' that filter is ignored. Meaning return all from that state or zonename.

I know this is the wrong way, but sort of gives an idea of what I am trying to do:

SELECT * 
FROM #tempb 

WHERE	CASE 
	     WHEN @State = 'all' THEN 
	     ELSE 'State = @State'
	END
	
	AND
	
	CASE 
             WHEN  @ZoneName = 'all' THEN 
             ELSE 'ZoneName = @ZoneName'
	END

Anyone care to point me in the right direction?

Thanks!

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-06-30 : 20:13:43

[code]
SELECT *
FROM #tempb
WHERE
(@State = 'all' or State = @State)
and (@ZoneName = 'all' or ZoneName = @ZoneName)
[/code]

elsasoft.org

JacquesThomas
Starting Member

6 Posts

Posted - 2007-06-30 : 21:10:24

Hi Chrispy

Seems that you might have the same type of problem as me. Maybe posts in my thread can point you in the right direction

JacquesThomas
Starting Member

6 Posts

Posted - 2007-06-30 : 21:23:11

Sorry there is url: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=85795

chrispy
Posting Yak Master

107 Posts

Posted - 2007-06-30 : 21:54:29

jezemine,
Thanks for the quick response. I have been working on your code and it is not working. Just so I am clear if @State = 'all' then I need all the states returned, if @State= 'PA', then I need just the rows from PA returned.

If I understand what you are trying to do then :


SELECT *
FROM #tempb
WHERE @State = 'all'

Should return all the states but it is not.

Or am I missing something?

Thanks again.

chrispy
Posting Yak Master

107 Posts

Posted - 2007-06-30 : 22:00:19

JacquesThomas,
Thanks for the help.

I am not sure the NullIf would work as the variables being passed are not null but would contain 'all'.

But I could be wrong.

chrispy
Posting Yak Master

107 Posts

Posted - 2007-06-30 : 22:27:27

jezemine,
Never mind!!! It works fine.

I had @state char(2) and @zonename char (2) which was cutting 'all' to 'al' and failing your compare.

Thanks a million.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources