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
