Pages

Apr 1, 2013

IF statement or condition in WHERE clause of SELECT statement in sql server

How to use or ass IF statement or condition in WHERE clause of SELECT statement in SQL server

We cannot use IF statement or condition in WHERE clause of the SELECT statement. It is syntactically incorrect. We can achieve our goal without using if condition in WHERE clause. For example:

(1) Suppose we want to execute the following query in SQL server:

SELECT*

FROM Student

WHERE

IF Age < 20 BEGIN

Country ='USA'

ENDELSEIF Age = 20 BEGIN

Counry ='UK'

ENDELSEBEGIN

Country ='India'

END

Its equivalent query in SQL server

SELECT*

FROM Student

WHERE

(Age < 20 AND Country ='USA')OR

(Age = 20 AND Country ='UK')OR

(Age > 20 AND Country ='India')

(2) Suppose we want to execute the following query in SQL server:

SELECT*

FROM Student

WHERE RollNo > 5

AND RollNo < 50

ANDIF Age < 20 BEGIN

Country ='USA'

ENDELSEIF Age = 20 BEGIN

Counry ='UK'

ENDELSEBEGIN

Country ='India'

END

Its equivalent query in SQL server

SELECT*

FROM Student

WHERE RollNo > 5

AND RollNo < 150

AND(

(Age < 20 AND Country ='USA')OR

(Age = 20 AND Country ='UK')OR

(Age > 20 AND Country ='India'))

(3) Suppose we want to execute the following query in SQL server:

SELECT*

FROM Student

WHEREIF Age > 10 AND AGE < 20 BEGIN

Country ='USA'

END

Its equivalent query in SQL server

SELECT*

FROM Student

WHERE

((Age > 10 AND AGE < 20 )AND Country ='USA')OR

(Age <= 10 OR Age >= 20)

If you still think that any condition which is possible using IF statement in WHERE clause of a SELECT statement but not possible by using AND or OR operator you can ask ii in the comment section I will write that.