Conditional Conditions in SQL

Question

I want to filter a query based on a conditional condition, for example if ProjectId equals 1 then filter based on some condition otherwise, another diffrerent condition:

something that- if possible in SQL- would look like this:

SELECT * FROM [dbo].[Commits] 
WHERE CASE WHEN ProjectId=1 THEN (CommitsCount > 2) ELSE (Name LIKE 'R%') END

Example:

ProjectId   Name      CommitsCount
----------- --------- -----------
1           Ahmed     2
1           Ahmed     6
2           Kamel     10
3           Rami      NULL

The result I need from the query is:

ProjectId   Name      CommitsCount
----------- --------- -----------
1           Ahmed     6
3           Rami      NULL

Gordon Linoff · Accepted Answer · 2019-01-13 12:57:45Z

2

Use case conditions in where clauses is not recommended. One reason is that they impede optimization of the query. Another is simply that they are not needed, so it make the conditions more complicated than necessary.

So I would recommend:

select c.*
from dbo.commits c
where (c.ProjectId = 1 and c.CommitsCount > 2) or
      (c.ProjectId <> 1 and c.Name = 'R&');

answered Jan 13, 2019 at 12:57

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Lukasz Szozda · Accepted Answer · 2019-01-13 12:46:41Z

1

You could use:

SELECT * 
FROM [dbo].[Commits] 
WHERE (CASE WHEN ProjectId  = 1 AND CommitsCount > 2 THEN 1
            WHEN ProjectId != 1 AND Name LIKE 'R%'   THEN 1
      END) = 1

db<>fiddle demo

(Name ='R&') => Name LIKE 'R%'

edited Jan 13, 2019 at 12:46

answered Jan 13, 2019 at 12:36

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

2 Comments

mshwf Over a year ago

I've added an example, illustrating how should the result be

mshwf Over a year ago

Oh thanks I have a mistake in my query (used & instead of %), great answer!

Collectives™ on Stack Overflow

Conditional Conditions in SQL

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related