using case when condition in WHERE in SP

  • i have a SP with where condition

    as where id=@id and month=@month and Eid=@Eid

    wht i need is if @Eid=0 i need to display all the reocrds

    if Eid>0 then display only that particular reocrd

    i tried

    where id=@id and month=@month and Eid=

    case when @Eid=0 then Eid>0

    else

    Eid=@Eid

    End

    but its giving me error

    Incorrect syntax near '>'.

  • ssurekha2000 (9/27/2013)


    i have a SP with where condition

    as where id=@id and month=@month and Eid=@Eid

    wht i need is if @Eid=0 i need to display all the reocrds

    if Eid>0 then display only that particular reocrd

    i tried

    where id=@id and month=@month and Eid=

    case when @Eid=0 then Eid>0

    else

    Eid=@Eid

    End

    but its giving me error

    Incorrect syntax near '>'.

    You mean something like this?

    ...

    where

    id = @id and

    month = @month and

    ((@Eid = 0) or (Eid = @Eid))

  • Hi,

    Try with this code

    where id=@id and month=@month and Eid=

    case when @Eid=0 then case when Eid>0 then Eid else 0 end else @Eid end =Eid

  • getting incorect syntax near =

  • ssurekha2000 (9/30/2013)


    getting incorect syntax near =

    Have you tried what I posted earlier?

  • parulprabu (9/30/2013)


    Hi,

    Try with this code

    where id=@id and month=@month and Eid=

    case when @Eid=0 then case when Eid>0 then Eid else 0 end else @Eid end =Eid

    There's a syntax error and a pointless logical check in this code. Can you identify them, parulprabu?

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • parulprabu (9/30/2013)


    Hi,

    Try with this code

    where id=@id and month=@month and Eid=

    case when @Eid=0 then case when Eid>0 then Eid else 0 end else @Eid end =Eid

    Sorry for the erroneous solution given. This is the correct one

    [Code="sql"]

    where id=@id and month=@month and Eid=

    (case when @Eid=0 then case when Eid>0 then Eid else 0 end else @Eid end)

    [/code]

  • I really hate to rain on everyone's parade but it isn't necessary to use a CASE to accomplish what is asked.

    Please look at the solution I posted in my original response.

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

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