CASE Expression in WHERE Clause

Pinal Dave, Blogger and Speaker, posted about How to Write Case Statement in WHERE Clause? in which he showed a method to skip comparison of value if the value of parameter is empty.

Here is another method

Let us create the same data structure

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

Now the search condition is that first name and last name values are passed. If either of them is empty it should skip the condition. Pinal already showed a method. Here is another method that does the same job

DECLARE @FirstName VARCHAR(100)
SET @FirstName = ''

DECLARE @LastName VARCHAR(100)
SET @LastName = 'Dave' 

-- Pinal's method
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

-- Alternate method
SELECT FirstName, LastName
FROM Contacts
WHERE
    (FirstName = @FirstName) or (@FirstName='')
	AND
	(LastName = @LastName) or (@LastName='')

Both return the following result

FirstName                     LastName
----------------------------- -----------------------
Pinal                         Dave
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s