2

I've worked around this problem for a while but I figured there has to be an easier way to do it so I decided to make a post.

I've got a data filter parameter and I want to make a if/else branch or case branch in my where clause. If my @State variable is anything other than "All" I want to add the "And (state = @State)" to my where clause.

SELECT     timestamp, service, state
FROM         socketLog
WHERE     (timestamp BETWEEN dbo.fxMilitaryTime('02/01/12', 'mdy') 
    AND DATEADD(dd, 1, getdate())) AND (state = @State)
ORDER BY service

I attempted to get a if/else and a case statement to work in the where but I had syntax issues. I'm pretty sure that case and if statements are supported in a where clause but I can't get it working right.

Thanks to those that have time to assist.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
TWood
  • 2,563
  • 8
  • 36
  • 58

3 Answers3

3

Can't you just change the last bit to:

AND (@State = 'All' OR @State = state)

Unless I'm misunderstanding?!

Sir Crispalot
  • 4,792
  • 1
  • 39
  • 64
1

The WHERE clause below will be ignored if @State='All' or use it as a filter if set to anything else.

WHERE ('All' = @State or state = @State)
Zachary
  • 6,522
  • 22
  • 34
1

A winner is me.

Declare @State varchar(20)
set @State = 'All'
SELECT     timestamp, service, state
FROM         socketLog
WHERE     (timestamp BETWEEN dbo.fxMilitaryTime('02/01/12', 'mdy') AND DATEADD(dd, 1, getdate()))
and (State = @State OR (@State = 'All'))
ORDER BY service
go
TWood
  • 2,563
  • 8
  • 36
  • 58
  • That's what we said ...! Incidentally, you can use `CASE` inside `WHERE`. Check this out: http://stackoverflow.com/questions/206484/sql-switch-case-in-where-clause – Sir Crispalot Feb 15 '12 at 23:44
  • I appreciate the link on the case statement. Incidentally I posted the thread and worked out the solution before coming back to refresh the thread. That happens on like 1/10 of my threads. Something about posting on here and explaining the problem leads me to a fix, or I just blindly stumble into it (like what happened today). – TWood Feb 15 '12 at 23:52
  • It's always the way - it's like having someone come over and help with a problem, and by the time you've explained it to them you've figured it out (before they've said a word!) – Sir Crispalot Feb 15 '12 at 23:56
  • +1 - I feel bad because this happens to me too. Notice the major difference between your answer and the ones that posted faster is that they didn't copy all of the source code. One of my downsides is that I'm meticulous and I usually try to include the complete query, formatted nicely and correcting other issues too, but I've gotten better about one-liners like this one... – Aaron Bertrand Feb 15 '12 at 23:59