Post reply

Where Clause with case statement comparing NULL

  • February 1, 2016 at 4:51 am

    #307513

    Hi,

    I have a zone table as

    ZoneIDZoneDesc

    1North Zone

    2East Zone

    3West Zone

    4South Zone

    what i need is if the variable has value then records matching (=) the values are to be displayed else the where clause should be a (in) clause

    declare @ZoneId int =null

    select * from MstZones

    where ZoneID in (case @ZoneId

    when null then (select zoneid from MstZones)

    else @ZoneId

    end)

  • February 1, 2016 at 5:00 am

    #1855642

    If @ZoneId is set to 1, then you want just the one row, if it's null you want all 4?

    WHERE (@ZoneID IS NULL OR ZoneID = @ZoneID)

    OPTION(RECOMPILE)

    The option(recompile is very important. Without it you'll have terrible performance.

    https://www.simple-talk.com/content/article.aspx?article=2280

    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

  • February 1, 2016 at 5:56 am

    #1855651

    Thanks Gail Shaw, the solution works for what i had posted.

    But my appologies, actually the zoneid is passed as parameter in a sp and the sp has select query in which there are inner joins. And based on the zone id value being null or not null the where clause has to be executed. eg:

    select ------

    where zoneid = @ZoneId (if null then all records else only the matching id)

  • February 1, 2016 at 6:10 am

    #1855653

    That's exactly what the WHERE clause I wrote for you will do.

    Oh, and to emphasise, you MUST use the OPTION(RECOMPILE) clause as well.

    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

  • February 1, 2016 at 10:23 pm

    #1855906

    thanks Gail your solution worked for me.

Viewing 5 posts - 1 through 4 (of 4 total)

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