IF… ELSE clause is very handy and whenever you need to perform any conditional operation, you can achieve your results using it. But there are some limitations in IF… ELSE, and one of the limitations is that you cannot use it in WHERE clause.
Let me demonstrate the limitations.
USE AdventureWorks2012 GO DECLARE @City AS VARCHAR(50) SELECT BusinessEntityID , FirstName , LastName , City FROM [HumanResources].[vEmployee] WHERE IF @City IS NULL City='Renton' ELSE City=@City --OUTPUT
Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword ‘IF’.
Msg 102, Level 15, State 1, Line 10
Incorrect syntax near ‘City’.
Nevertheless, there are solutions which are given below:
SOLUTION 1 :
You can use CASE statement instead of IF..ELSE clause to do conditional where clause.
Given below is the script.
--This script is compatible with SQL Server 2005 and above. USE AdventureWorks2012 GO DECLARE @City AS VARCHAR(50) SELECT BusinessEntityID , FirstName , LastName , City FROM [HumanResources].[vEmployee] WHERE City = (CASE WHEN @City IS NULL THEN 'Renton' ELSE @City END) ORDER BY BusinessEntityID --OUTPUT
SOLUTION 2 :
In this solution, you can use IIF clause instead of IF..ELSE to do conditional where clause.
Given below is the script.
--This script is compatible with SQL Server 2012 and above USE AdventureWorks2012 GO DECLARE @City AS VARCHAR(50) SELECT BusinessEntityID , FirstName , LastName , City FROM [HumanResources].[vEmployee] WHERE City = IIF (@City IS NULL, 'Renton', @City) ORDER BY BusinessEntityID --OUTPUT
SQL Server Portal 
This is almost exactly what I’m looking for. How would I go about selecting ALL Cities if @City was NULL
Here is the solution.
https://raresql.com/2013/10/08/sql-server-how-to-implement-condition-where-clause/
Got it – coalesce(@City,City)
Greetings!
What: A query to return the latest “StartDate”
When: When reviewing 103,100 records
Where: For each ItemInternalKey may have multiple “StartDate(s)”
Who:
Which: Allows a view to be created that returns only the last “StartDate” for each “ItemInternalKey” unique record.
How:
1. Using an iif statement to identify the last “StartDate”.
2. HERE IS THE PROBLEM….. Need to create a where statement taking the results of the IIF statement filtering for “Pick_Me”.
This is where I am stuck.
Any advice would be greatly appreciated.
Bartley
++++++++++++++++++++++++++++++++++++++++++++
DECLARE @Last_Value as varchar(15);
SELECT [MatrixMemberId]
,[StoreHierarchyKey]
,[SupplierInternalKey]
,[ItemInternalKey]
,[ReceivingUnitId]
,[Ratio]
,[PriceTypeId]
,[StartDate],
last_value(startdate) OVER (ORDER BY iteminternalkey) AS LastValue
,iif([StartDate]=(last_value(startdate) OVER (ORDER BY iteminternalkey)),’Pick_Me’,’Nope’) as Last_Value
,[EndDate]
,[ReceivingUnitCost]
,[UpdatedBy]
,[UpdatedDate]
,[PublicationStatus]
,[PublicationRowVersion]
,[ChangeBatchId]
FROM [MP_Bosselman].[dbo].[CostListHierarchy_ALL]
/*Where LAST_VALUE =
(CASE WHEN @Last_Value IS NULL THEN ‘Pick_Me’ else @Last_Value End)*/
order by ItemInternalKey