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 > 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
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.
Thanks...Finally got the hang of conditional statements in WHERE clauses for SQL.
ReplyDeleteThank yoy so so Much. I had been finding for a solution for this from a long time.Thanks a million
ReplyDeleteselect d.Document_Name, d.Entry_Date, d.Person_ID, cv.Value, dc.Cust_String_Val
ReplyDeletefrom 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
select d.Document_Name, d.Entry_Date, d.Person_ID, cv.Value, dc.Cust_String_Val
Deletefrom 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'))
)
SELECT a,b,c
ReplyDeleteFROM Documents as d
WHERE
IF (@input !='') CONTAINS(a, @input)
SELECT a,b,c
DeleteFROM Documents as d
WHERE ((@input !='' AND a LIKE '%' + @input + '%' ) OR @input = '')
But that's not using FullText search, so you will get different results.
DeleteDeclare @paramFromDestination nvarchar(10)= 'LHR'
ReplyDelete,@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)))
)
Sir , i have try to use case instead of if but i have nested if statements so please explore it
ReplyDeleteVery Helpful
ReplyDeleteThank you very much. Seeing these gave me ideas for what I was trying to do!
ReplyDelete