How to write Conditional Where Clause

  • Hi,

    I have a simple query

    select name from tableInfo

    i am passing two parameters to the stored procedure

    @x and @y

    if @x!=0 and x is not null i want to add that in where clause for exp.

    select name from tableInfo where x=@x

    Similarly for @y

    select name from tableInfo where x=@x and y=@y

    I had use dynamic query earlier but i want to avoid it.

  • http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • try along these lines .....

    select * from Tname

    where (Tcol1 = @X OR Tcol1 is null)

    AND (Tcol2 = @Y OR Tcol2 is null)

    ---- OR -----

    select * from Tname

    where Tcol1 = case when @X is null then null

    when @X = 0 then null

    else @X end

    and

    Tcol2 = case when @Y is null then null

    when @Y = 0 then null

    else @Y end

  • Hi dilip,

    select * from Tname

    where Tcol1 = case when @X is null then null

    when @X = 0 then null

    else @X end

    and

    Tcol2 = case when @Y is null then null

    when @Y = 0 then null

    else @Y end

    I tried this , but when i pass @x=0 it doesnt works. The Case makes it Tcol1=null.

  • [font="Verdana"]Hi

    SELECT [NAME] FROM TABLEINFO(NOLOCK)

    WHEREX = CASE WHEN @X 0 AND @X IS NOT NULL THEN @X ELSE X END

    ANDY = CASE WHEN @Y 0 AND @Y IS NOT NULL THEN @Y ELSE Y END

    Regards[/font]

  • :w00t: HI Thank you it worked !! :w00t:

  • Just a couple things with that.

    It's likely to perform badly. Take a look at the blog post I referenced earlier. Also, before you use nolock, read this: http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (6/17/2009)


    Just a couple things with that.

    It's likely to perform badly. Take a look at the blog post I referenced earlier. Also, before you use nolock, read this: http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx

    Hi Gila,

    As i am a newbie i donno so,

    What bad effect will the query with 'case in where clause' bring ?

  • Poor performance. Generally that kind of query does not use indexes effectively and often does table scans. On small tables that aren't used too often that won't be a major issue. On larger tables or busy systems it is.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Hi,

    Half of the code is already in your question..change the and/or in the where clause according to ur requirement

    SELECT[name]

    FROMtableInfo

    WHERE(@x 0 and @x is not null and x = @x)

    and/or(@y 0 and @y is not null and y = @y)

  • Thank you Pyay Nyein.

    That is one of the most useful answers. Makes the whole query look way simpler than CASE statements in the WHERE clause.

  • GilaMonster's warning about poor performance and the link mentioned rates as a most useful answer.

  • For all those that have posted solutions, you seriously need to read Gail's "Catch All Query" article. Please refer to her first post on this thread.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 13 posts - 1 through 12 (of 12 total)

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