Results 1 to 11 of 11

Thread: Case statement in where clause

  1. #1
    Join Date
    Jan 2003
    Location
    Hyderabad
    Posts
    23

    Case statement in where clause

    can i use case statement in where clause.
    The scenario is as follow

    declare @param int
    select
    *
    from
    table1
    where
    column1 = 'asdf'
    column2= @param

    In the above sql, if @param is '' then i don't want to include it in the where clause.I can use "like" statement for that, but i want exact value not partial value.


    can i use case in where clause so that if @param is '' then i will not include in the where clause

  2. #2
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    why cant your use IF .....begin....end conditions

  3. #3
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    You can't use CASE in WHERE clause.

  4. #4
    Join Date
    Aug 2003
    Location
    Vancouver
    Posts
    2

    Cool Case in Where clause

    Not true. You can use Case in the Where clause

    Select * from table
    where
    1 = CASE
    When column1 = 'asdf' then 1
    When column2= @param then 1
    Else 0
    END

  5. #5
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    it will not work.

    gvraghuramraju wants to ignore 'and column2 =@param' when @param=''

    create table table1 (column1 varchar(10), column2 int)
    insert into table1 select 'asdf',1
    insert into table1 select 'asdf1',2
    insert into table1 select 'asdf',NULL


    For example if @param is NULL he wants the statement to be like this

    select * from table1 where column1 = 'asdf'


    For example if @param is not NULL he wants the statement to be like this

    select * from table1 where column1 = 'asdf' and column2 = @param

  6. #6
    Join Date
    Aug 2003
    Location
    Vancouver
    Posts
    2

    Cool What about this

    Select * from table
    where 1= CASE
    when @param is null and column1='asdf' then 1
    when @param is not null and column1='asdf' and colummn2 = @param then 1
    else 0
    END

    Just an idea. Syntactically, the statement is corrent but you may alter the statement to address the requirements.

  7. #7
    Join Date
    Aug 2003
    Posts
    15

    You can!

    In the above sql, if @param is '' then i don't want to include it in the where clause.I can use "like" statement for that, but i want exact value not partial value.

    The condition above where @param is '' or not to be included is same as having a default value and having an OR condition

    select
    *
    from
    table1
    where
    column1 = 'asdf'
    column2= @param

    Can be written as


    select
    *
    from
    table1
    where
    column1 = 'asdf' And ((column2= @param) Or column2='')

    In the above statement if u work out the logic table
    If value in @param is equal to column2 then it evaluates to
    ((column2 = @param) Or column2='')
    ---True--- Or ---False---
    The return value of the whole expression is true

    In the condition where @param is '' then the same expression again evaluates to True. there fore will not affect the overall logic of the where clause.

    But if @param is something other than column2 and is not equal to '' then the expression evaluates to false.

    and ur requirement is satisfied. Let me know if u understand the logic.

    Ketan

  8. #8
    Join Date
    Aug 2003
    Location
    In a galaxy far, far away...
    Posts
    28
    Maybe a stupid idea but:

    declare @param int,@command char(255)

    {...}

    if @param <> ''
    select &command = 'select * from table1 where column1 = ''asdf'' and column2 = ' + convert(char(10),@param)
    else
    select &command = 'select * from table1 where column1 = ''asdf''


    exec (@command)


    This should work if I did not make a big mistake.

  9. #9
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    I already answered it on the begining of this thread.

  10. #10
    Join Date
    Jan 2003
    Location
    Hyderabad
    Posts
    23

    ThanQ

    ThanQ guys for ur replies.

  11. #11
    Join Date
    Jan 2003
    Location
    Hyderabad
    Posts
    23

    It's Working

    The solution by k2kataria solved my problem .Thanq very much for ur solution and thanks for ur good logic

Posting Permissions

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