Conditional Where Clause

I didn’t write any post on my blog for quite long time. Today, I spend some times to write a post. Actually, this topic is saved in my draft for quite long also. :) And I received one topic from SQL Server Central which is same as my topic. We know last time that we cannot put conditional filter in where clause of T-SQL statements. But that’s not true that I find out one thing which can use conditional filter in where clause.

I'm having a request from my customer to generate dates which are based on their shifts, 135 and 246. To get all dates in a month is not difficult which I already posted in Get All Dates section. So I'm spending some times to get that conditional where clause as follow:

DECLARE @Days AS varchar(3)
SET @Days='135'

SELECT Days FROM GetAllDates
WHERE datename(dw, Days) IN (CASE WHEN SUBSTRING(@Days, 1, 1)='1' THEN 'Monday'
WHEN SUBSTRING(@Days, 1, 1)='2' THEN 'Tuesday' END,
CASE WHEN SUBSTRING(@Days, 2, 1) = '3' THEN 'Wednesday'
WHEN SUBSTRING(@Days, 2, 1) = '4' THEN 'Thursday' END,
CASE WHEN SUBSTRING(@Days, 3, 1) = '5' THEN 'Friday'
WHEN SUBSTRING(@Days, 3, 1) = '6' THEN 'Saturday' END)

The following is some another example of conditional where clause from SQL Server Central.

SELECT *
FROM Production.Product
WHERE
ProductNumber LIKE CASE
WHEN @ProductNumber IS NULL THEN '%'
ELSE @ProductNumber END
AND Color LIKE CASE
WHEN @Color IS NULL THEN '%'
ELSE @Color END

SELECT *
FROM Production.Product
WHERE ReorderPoint =
CASE
WHEN @ReorderPoint IS NULL THEN ReorderPoint
ELSE @ReorderPoint
END