Using Case In Combination With Where To Specify Different Columns To Filter By

This probably isn't valid SQL, but this is what I'm trying to do: SELECT * FROM tablename WHERE (CASE @ScoreType WHEN 1 THEN score1 WHEN 2 THEN score2

Solution 1:

this should work maybe?

SELECT*FROM tablename
WHERE 
    (CASE@ScoreTypeWHEN1THEN score1 
        WHEN2THEN score2 
        WHEN3THEN score3
      END) >=@Score

---

Edit:

I think the problem is: you forgot the end to the case

Solution 2:

You want something like this:

SELECT*FROM tablename
WHERE (@ScoreType=1AND score1 >=@Score) OR 
      (@ScoreType=2AND score2 >=@Score) OR
      score3 >=@score

Edit:

As @HABO pointed out, the above is subtly different to your original code. Replace with:

SELECT * 
FROMtablenameWHERE (@ScoreType = 1 AND score1 >= @Score) OR 
      (@ScoreType = 2 AND score2 >= @Score) OR
      (@ScoreType <> 1 AND @ScoreType  <> 2 AND score3 >= @score) 

Solution 3:

This should work:

SELECT*FROM tablename
WHERE@Score<=CASE@ScoreTypeWHEN1THEN score1 
        WHEN2THEN score2 
        ELSE score3 END

Solution 4:

SELECT *
FROMtablenameWHERECASEWHEN@ScoreType = 1 THEN score1
WHEN@Scoretype = 2 THEN score2
WHEN@Scoretype = 3 THEN score3
END

Not sure what you're trying to achieve with

[>= @Score]

Thanks for the edit guys, silly mistake on my part.