eugeneK eugeneK - 1 year ago 62
SQL Question

Is there a way to avoid conditions in TSQL?

I'm using SQL Server 2005 with 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

I get these parameters from first time use performs a search and save his search preferences in search table and then use them on
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 query'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 Source

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

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download