Is there a way to avoid conditions in TSQL?

Question

Hi, i'm using sql-server 2005 with asp.net C#.

There is a search query on my site with different parameters.

fromAge as tinyint
toAge as tinyint
fromHeight as tinyint
toHeight as tinyint
gender as tinyint
withImage as bit
region as tinyint
astrologicaSign as tinyint

these parameters i get from first time use performs a search and save his search preferences in Search Table and then use them on Users Table from which i select users that meet with requirements.

Problem is that some values can be conditional like for example withImage(bit) this means that now i need to have if statement that check whether i provided 0 or 1 to withImage and then perform select ie. if withImage=1 then querie's where would be picture1<>'0' else without where condition at all.

I did end up with 10 nested if statements with initial query ( which i simplified for example sake).

Is there way to avoid it except dynamic SQL?

Answer 1

This is quite simply achieved using an AND statement

SELECT * FROM User 
WHERE (withImage =1 AND picture1<>'0') OR withImage=0

You can then add similar clauses for each element. Note that if the logic gets more complicated you can also use CASE statements in the WHERE clause.

Answer 2

If you can align the parameter values you are passing to be equal to the values you want to retreive (or at least always do an equals comparison) then you can use CASE WHEN quite efectively like this

SELECT * FROM User 
WHERE picture1 = CASE WHEN @WithImage = 1 THEN @withImage ELSE picture1 END

That way it is comparing the picture1 field with the parameter if it is 1 or comparing the field with itself if it is not.

Answer 3

Others have given you a solution but honestl , this is one case where dynamic SQL is likely to improve performance. I'm not a big dynamic SQL fan, but this is one case where it does a better job than most anything else.