CASE statement in WHERE clause

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)

CASE statement in WHERE clause

Author

Topic

Broodmdh
Starting Member

2 Posts

Posted - 2007-07-31 : 12:51:01

I am trying to write a WHERE clause that utilizes CASE (or IF), but I'm not having any luck. I'm trying to manage something like the following:


...
WHERE x.Var1 = @var1
AND (CASE @var2
         WHEN 'Y' THEN x.Var3 >= @Var3 END
         WHEN 'N' THEN x.Var3 > @Var3 END
     END)
AND ...

Is this sort of thing possible? I haven't been able to get it to work so far.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2007-07-31 : 13:00:27

Maybe:

...
WHERE x.Var1 = @var1
    AND x.Var3 >= 
        CASE @var2
            WHEN 'Y' THEN @Var3
            ELSE @Var3 + 1
        END
    AND ...

X002548
Not Just a Number

15586 Posts

Posted - 2007-07-31 : 13:01:30

WHERE x.Var1 = @var1
AND (@var2 'Y' AND x.Var3 >= @Var3) OR (@var2 'N' AND x.Var3 > @Var3)

?????

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Broodmdh
Starting Member

2 Posts

Posted - 2007-07-31 : 13:21:24

Ifor's suggestion worked well. How would you make this work for something like the following:


AND x.Var4 IN
CASE @Var4
    WHEN 'A' THEN (1)
    WHEN 'I' THEN (0)
    WHEN 'B' THEN (0,1)
END

This throws the following error for me: "Incorrect syntax near the keyword 'CASE'".

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources