Tuesday, June 5, 2018

Case Statement in SQL WHERE Clause

DECLARE @FirstName VARCHAR(100)
SET @FirstName = ''
 
DECLARE @LastName VARCHAR(100)
SET @LastName = 'John' 
 
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