Optional condition in select statement

Question

Post reply

Optional condition in select statement

Mark Green

SSCrazy

Points: 2085
More actions
August 2, 2006 at 9:32 am

#115941

I would like to be able to put an optional condition in a Select statement.
I have a variable called @variable and if this variable is 'x' I would like to be able to set an additional condition in the WHERE part of the SELECT statement. If @variable = anything else I can not have the WHERE sub condition activated.
Please can someone show me how this is done.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Aaron Ingold SSCrazy Eights Points: 9070 More actions · Answer 1

This should do it:

SELECT *

FROM table

WHERE (@variable = 'x' AND other_condition = 'foobar')

OR ISNULL(@variable, 'anything other than x') 'x'

The ISNULL() is important because otherwise if @variable is null the statement returns nothing (problems with matching to null and all).

Mark Green SSCrazy Points: 2085 More actions · Answer 2

Interesting looking code. I'm not sure if I fully understand the logic of it. In fact, I don't believe that it will work.

Aaron Ingold SSCrazy Eights Points: 9070 More actions · Answer 3

Well let me know...

The logic is that if @variable = 'x' then it will apply the rest of the AND conditions within the parenthesis. However if @variable is not 'x', then it just returns all rows, not applying a WHERE condition to filter the results. That's how I read your request.

Mark Green SSCrazy Points: 2085 More actions · Answer 4

It is this bit that I have a problem with:

ISNULL(@variable, 'anything other than x') <> 'x'

Aaron Ingold SSCrazy Eights Points: 9070 More actions · Answer 5

You probably need to read up on ANSI_NULLS settings in BOL (or http://www.sqlservercentral.com/columnists/jtravis/understandingthedifferencebetweenisnull.asp). Evaluating anything against nulls can be tricky and depending on your settings the select statement could have returned nothing if @variable was a null value. ISNULL() allows you to correct for the possibility of null value comparisons. You could also write the last line like this (if it's easier to understand) and get the same result:

OR @variable 'x'

OR @variable IS NULL

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

OR you could always write 2 different stored procs and call the correct depending on the parameter value. That also guarantees to always have to optimal plan for the query.