Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Posts
    53

    Talking Unanswered: case statement in where clause

    Hello

    I want to put a case statement into a where clause but it's not working. Can anybody help, or tell me a better way of doing this

    Thanks very much

    declare @param varchar (100)
    select @param = 'mytext

    select
    colA
    ,colB
    ,colC
    from
    mytable
    where
    (case
    when @param is null then colA = 'group'
    else colA = 'single'
    end)

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd suggest using
    Code:
    WHERE colA = CASE WHEN @param IS NULL THEN 'group' ELSE 'single' END
    -PatP

  3. #3
    Join Date
    Jan 2004
    Posts
    53
    No I can't do it like that

    I've rewritten what I want, maybe you can hlp with this

    declare @param varchar (100)
    select @param = 'mytext'


    select
    colA
    ,colB
    ,colC
    from
    mytable
    where
    (case
    when @param is null then colB = 'group' and colC = 'something'
    else colB = 'group' and colC = @param
    end)

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Ok, let's do that dance and move on to:
    Code:
    SELECT 
       colA
    ,  colB
    ,  colC
       FROM mytable
       WHERE (@param IS NULL AND colB = 'group' AND colC = 'something')
          OR (@param = colC  AND colB = 'group')
    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •