CODEWITHSUNDEEP
sqlwhere-clause
GLP
GLP

Reputation: 3675

How to write a where clause that has multiple cases?

I have a stored procedure that has a nullable parameter, and following is my query

select * 
from table1
where table1.id = isnull(@id, table1.id)

Now there are some special ids I treat them differently. I am adding another table table2 like below

combid    id
1         abc01
1         abc02
1         abc03
2         hig01
2         hig02

I have to change the query to satisfy following cases

  1. If @id is null, the where clause will be table1.id = table1.id
  2. If @id is not null,
    2.1 if @id exists in table2, the where clause will be table1.id in (select id from table2 where combid in (select combid from table2 where id=@id))
    2.2 otherwise the where clause will be table1.id = @id

I have tried following query, but doesn't work.

select  * from table1
where (table1.id=@id and not exists(select * from table2 where id=@id)
or @id is null
or table1.id in (select id from table2 where combid in (select combid where id=@id)) and  exists(select * from table2 where id=@id))

How do I change the stored procedure's query?

Upvotes: 0

Views: 2002

Answers (2)

Nenad Zivkovic
Nenad Zivkovic

Reputation: 18559

SELECT * FROM table1
WHERE
    @id IS NULL
    OR
    (
        @id IS NOT NULL
        AND
        (      
            (
                EXISTS (SELECT * FROM TABLE2 WHERE id = @id)
                AND
                table1.id IN (SELECT id FROM table2 WHERE combid in (SELECT combid FROM table2 WHERE id=@id))
            ) 
            OR
            (
                NOT EXISTS (SELECT * FROM TABLE2 WHERE id = @id)
                AND         
                table1.id = @id
            )
        )
    )

Upvotes: 1

ljh
ljh

Reputation: 2594

You can use if...else...instead of writing only one SELECT statement, using if else for your case is easier to read. 


if(@id is null)
    select * from table1
else if exists (select 1 from table2 where id = @id)
    select * from table1 
    where table1.id in 
                  (select id from table2 where combid in 
                         (select combid from table2 where id=@id)
                  )
else 
   select * from table1 where table1.id=@id

Upvotes: 0

Related Questions