CODEWITHSUNDEEP
sql-serversql-server-2008
IUnknown
IUnknown

Reputation: 9819

If-else in where clause SQL-Server

In SQL-Server 2008 how do I achieve the following objective - 

select * from student 
where 
(
   (if studentId is not null then  studentId='x')
   else
   (firstName='abc' and age > 26)
)

I would like to avoid re-writing the entire select clause for either conditions separately.

Upvotes: 0

Views: 51

Answers (2)

John Smith
John Smith

Reputation: 7407

You could use a case statement in the where clause. You could almost think of a case statement like SQL's "set based" version of an if statement - 

select * 
from student 
where 
    case 
        when studentId is not null and studentId = 'x'
            then 1
        when firstName='abc' and age > 26
            then 2
        else 0
    end <> 2

More on the case statement-

https://msdn.microsoft.com/en-us/library/ms181765.aspx

http://sqlschool.modeanalytics.com/intermediate/case.html

Upvotes: 0

Felix Pamittan
Felix Pamittan

Reputation: 31879

You can simply use an OR in your WHERE clause:

SELECT *
FROM student
WHERE
    studentId = 'x'
    OR (firstname = 'abc' AND age > 26)

Upvotes: 2

Related Questions