SQL Server Performance

"case-when" as part of "where" in a "select" query, i have a problem

Discussion in 'T-SQL Performance Tuning for Developers' started by pssheba, Sep 24, 2007.

  1. pssheba New Member

    Hi everyone,
    I'd like to display the email addresses of those who are 'vip's or those who are'nt , depends on a variable. My code goes like:
    declare @xxx varchar(20)
    set @xxx='vip'
    select email
    from
    fiftyplus..contact1
    where department=
    case @xxx
    when
    'vip'
    then
    'vip'
    else
    <>'vip'
    end

    the '<>vip' at the end of the query is wrong of course but i have no idea how to write it correctly.
    I wonder if anyone can help me with that.
    Thanks a lot
  2. ndinakar Member

    If I understand your question correctly, you dont need the <> because the control will come to the ELSE part of the CASE only if @xxx <> 'vip'. So its kinda redundant.declare
    @xxx varchar(20)set
    @xxx='vip'select emailfromfiftyplus..contact1where department
    = (case @xxx when 'vip' then 'vip' else 'Not vip' end )
  3. pssheba New Member

    Hi mvp,
    There is no value such as 'No vip'. There are 'vip', 'member', 'candidate', 'left' etc...
    What i want is: If the parameter is 'vip', to show all vip's. If it isnt (e.g: '1', 'ttt', '0' etc..) I want to display each row unless it has 'vip' inside the 'department' column.
    Hopefully i gave a better explanation now,
  4. ndinakar Member

    Actually I didnt understand "I want to display each row unless it has 'vip' inside the 'department' column."
    Can you show with an example..perhaps a few rows with VIP and without and expected output?
  5. pssheba New Member

    Hi ,
    My table in short looks like:
    name status email
    aaa vip aaa@aaa
    bbb memeber bbb@bbb
    ccc candidate ccc@ccc
    I'm writing a procedure to display memebers using a parameter. if the parameter is 'vip' the sub procedure will show the following row:
    name status email
    aaa vip aaa@aaa
    If the parameter is any but 'vip', the sub procedure will show
    name status email
    bbb member bbb@bbb
    ccc candidate ccc@ccc
    So i wrote the following query in the sub procedure:
    declare @xxx varchar(20)
    set @xxx='vip'

    select *
    from
    fiftyplus..contact1

    where
    department=
    case @xxx
    when
    'vip'
    then
    'vip'
    else
    <>'vip'
    end
    It should show only the vip member if the parameter is 'vip' or all the members, except the 'vip' member if the parameter is not 'vip'.
  6. pssheba New Member

    oops.. it should be "status" instead of department...
  7. Madhivanan Moderator

    Simple approach
    declare @xxx varchar(20)
    set @xxx='vip'
    If @xxx='vip'
    select *
    from
    fiftyplus..contact1

    where
    department='vip'
    else
    select *
    from
    fiftyplus..contact1

    where
    department<>'vip'
  8. pssheba New Member

    Thanks a lot !

Share This Page