Post reply

Optional condition in select statement

  • 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.

     

  • August 2, 2006 at 9:47 am

    #652863

    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).

  • August 2, 2006 at 9:52 am

    #652866

    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.

  • August 2, 2006 at 10:06 am

    #652878

    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.

  • August 2, 2006 at 10:09 am

    #652882

     

    It is this bit that I have a problem with:

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

  • August 2, 2006 at 10:24 am

    #652887

    You probably need to read up on ANSI_NULLS settings in BOL (or http://qa.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

  • August 3, 2006 at 6:46 am

    #653062

    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.

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

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