SQL - CASE Statement in WHERE Clause

Example 1

SELECT FirstName, LastName, PersonType

FROM   Person.Person

WHERE  1 = CASE

              WHEN PersonType = 'VC' THEN 1

              WHEN PersonType = 'IN' THEN 1

              ELSE 0

           END

To comes down to readability. In this case, the logic is pretty simple. Just use Boolean OR or the IN operator.

SELECT FirstName, LastName, PersonType

FROM   Person.Person

WHERE  PersonType = 'VC' or PersonType = 'IN'

Example with SQL IN

SELECT FirstName, LastName, PersonType

FROM   Person.Person

WHERE  PersonType in ('VC','IN')

Example 2
USE tempdb

GO

CREATE TABLE Contacts(ID INT, FirstName VARCHAR(100), LastName VARCHAR(100))

INSERT INTO Contacts (ID, FirstName, LastName)

SELECT 1, 'Pinal', 'Dave'

UNION ALL

SELECT 2, 'Mark', 'Smith'

UNION ALL

SELECT 3, 'Mohan', 'Shah'

UNION ALL

SELECT 4, 'Matt', 'Alexus'

UNION ALL

SELECT 5, 'Roger', 'Croc'

GO

SELECT FirstName, LastName

FROM Contacts

GO

DECLARE @FirstName VARCHAR(100)

SET @FirstName = ''

DECLARE @LastName VARCHAR(100)

SET @LastName = 'Dave'

SELECT FirstName, LastName

FROM Contacts

WHERE 

    FirstName = CASE

    WHEN LEN(@FirstName) > 0 THEN  @FirstName

    ELSE FirstName

    END

AND

    LastName = CASE

    WHEN LEN(@LastName) > 0 THEN  @LastName

    ELSE LastName

    END

GO