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'
    END ELSE IF Age = 20 BEGIN
         Counry = 'UK'
    END ELSE BEGIN
         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
    AND IF Age < 20 BEGIN
         Country = 'USA'
    END ELSE IF Age = 20 BEGIN
         Counry = 'UK'
    END ELSE BEGIN
         Country = 'India'
    END

Its equivalent query in SQL server
   
SELECT *
FROM Student
WHERE RollNo >
    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
WHERE IF 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.

11 comments:

Anonymous said...

Thanks...Finally got the hang of conditional statements in WHERE clauses for SQL.

Anonymous said...

Thank yoy so so Much. I had been finding for a solution for this from a long time.Thanks a million

Anonymous said...

select d.Document_Name, d.Entry_Date, d.Person_ID, cv.Value, dc.Cust_String_Val
from Document as d, Document_Custom as dc, Custom_FixedValues as cv
where cv.Custom_ID = dc.Custom_ID
and dc.Document_ID = d.Document_ID
and d.Document_Name = '(HR3) Education- ABEL, LAURIE'
if cv.Value = 'A' begin cv.Value = 'null' end
else if cv.Value = 'I' begin cv.Value = 'null' end
else begin cv.Value = cv.Value end

Priyanka kumari said...

select d.Document_Name, d.Entry_Date, d.Person_ID, cv.Value, dc.Cust_String_Val
from Document as d, Document_Custom as dc, Custom_FixedValues as cv
where cv.Custom_ID = dc.Custom_ID
and dc.Document_ID = d.Document_ID
and d.Document_Name = '(HR3) Education- ABEL, LAURIE'
((cv.Value IN('I', 'A') AND cv.Value = 'null') OR
(cv.Value NOT IN('I', 'A'))
)

Anonymous said...

SELECT a,b,c
FROM Documents as d
WHERE
IF (@input !='') CONTAINS(a, @input)

Priyanka kumari said...

SELECT a,b,c
FROM Documents as d
WHERE ((@input !='' AND a LIKE '%' + @input + '%' ) OR @input = '')

Anonymous said...

But that's not using FullText search, so you will get different results.

Unknown said...

Declare @paramFromDestination nvarchar(10)= 'LHR'
,@paramToDestination nvarchar(10)= 'DPS'
,@paramAirline nvarchar(10)= null
,@paramFromDate datetime= '2014-07-25'
,@paramToDate datetime= '2014-08-28'
,@paramExclude bit= 0

print(@paramExclude)
SELECT
[AirOfferBlackout_FromDestination]AS FromDestination
,[AirOfferBlackout_ToDestination]AS ToDestination
,[AirOfferBlackout_Airline]AS Airline
,[AirOfferBlackout_OutBoundFromDate]AS OutBoundFromDate
,[AirOfferBlackout_OutBoundToDate]AS OutBoundToDate
,[AirOfferBlackout_InBoundFromDate]AS InBoundFromDate
,[AirOfferBlackout_InBoundToDate]AS InBoundToDate

FROM [AirOfferBlackout]
WHERE ([AirOfferBlackout_FromDestination] = COALESCE(@paramFromDestination,[AirOfferBlackout_FromDestination]) OR [AirOfferBlackout_FromDestination] = 'ANY')
AND ([AirOfferBlackout_ToDestination] = COALESCE(@paramToDestination,[AirOfferBlackout_ToDestination]) OR [AirOfferBlackout_ToDestination] = 'ANY')

AND ([AirOfferBlackout_Type] = COALESCE(@paramExclude,1))
AND ([AirOfferBlackout_Airline] = COALESCE(@paramAirline,[AirOfferBlackout_Airline]) OR [AirOfferBlackout_Airline] = 'ANY')
AND
(
((@paramFromDate > (CONVERT(varchar(20),[AirOfferBlackout_OutBoundFromDate],106)) AND @paramFromDate < (CONVERT(varchar(20),[AirOfferBlackout_InBoundToDate],106))) AND ((@paramToDate > (CONVERT(varchar(20),[AirOfferBlackout_OutBoundFromDate],106))) AND @paramToDate < (CONVERT(varchar(20),[AirOfferBlackout_InBoundToDate],106))))

OR
(
if(((CONVERT(varchar(20),[AirOfferBlackout_OutBoundFromDate],106)) = '01-01-1900') AND ((@paramToDate < (CONVERT(varchar(20),[AirOfferBlackout_InBoundFromDate],106))))
if ((@paramToDate < (CONVERT(varchar(20),[AirOfferBlackout_InBoundFromDate],106))) OR (@paramToDate > (CONVERT(varchar(20),[AirOfferBlackout_InBoundToDate],106)))))

)
--OR
--if((CONVERT(varchar(20),[AirOfferBlackout_OutBoundFromDate],106) = '01-01-1900') AND ((@paramFromDate < (CONVERT(varchar(20),[AirOfferBlackout_InBoundFromDate],106))) AND (@paramToDate > (CONVERT(varchar(20),[AirOfferBlackout_InBoundToDate],106)))))
--if (@paramFromDate > (CONVERT(varchar(20),[AirOfferBlackout_OutBoundFromDate],106)) AND (@paramFromDate < (CONVERT(varchar(20),[AirOfferBlackout_InBoundToDate],106))))
--OR

-- if((CONVERT(varchar(20), [AirOfferBlackout_InBoundFromDate],106)) = '01-01-1900')
-- if ((@paramToDate > (CONVERT(varchar(20),[AirOfferBlackout_InBoundFromDate],106))) AND @paramToDate > (CONVERT(varchar(20),[AirOfferBlackout_OutBoundFromDate],106)))
)

Unknown said...

Sir , i have try to use case instead of if but i have nested if statements so please explore it

Unknown said...

Very Helpful

DzSoundNirvana said...

Thank you very much. Seeing these gave me ideas for what I was trying to do!