Feeds:
Posts
Comments

Posts Tagged ‘Conditional Where clause’

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

ifelse1.1

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

ifelse1.1

Read Full Post »