CASE WHEN Statement Examples

-- this is similar to ' SELECT * FROM Customers WHERE Country = 'France' '
SELECT * FROM Customers
WHERE CASE WHEN Country = 'France' THEN 1 END = 1

Nested CASE WHEN examples:

declare @test varchar(10);
set @test = 'debug';
SELECT  * FROM Customers
WHERE 1 = (	CASE 
		WHEN @test = 'debug'
		THEN	CASE WHEN Country = 'France' AND City = 'Marseille' 
				THEN 1 
				END  
		END )	

declare @test varchar(10);
set @test = 'debug';
SELECT  * FROM Customers
WHERE 1 = (	CASE 
			WHEN @test = 'debug' 
			THEN	CASE WHEN Country = 'France'  
					THEN	CASE
							WHEN  City = 'Marseille'
							THEN 1
							ELSE NULL
							END
					END  
			END )	

This one actually will give the same result as the previous query (2nd example on this article). Below is the result:

Using LIKE clause:

declare @test as varchar(50)
set @test = 'abcdef'
SELECT	CASE 
		WHEN @test LIKE '%bc%' THEN 1
		WHEN @test LIKE '%de%' THEN 2
		ELSE 0
		END	

The result is '1' because the first condition is the first match.

An example of further filtering the rows' cities given their countries:

SELECT * FROM CUSTOMERS
WHERE City = (	CASE WHEN Country = 'France' THEN 'Marseille'
					 WHEN Country = 'UK' THEN 'London'
					 WHEN Country = 'Spain' THEN 'Madrid'
					 END  )
ORDER BY Country

The result is: