The Stored Procedure for Searching Challenge

The Problem

You have a table chock full of good business data. This data needs to be accessed by a host of potential search criteria and any combination of that search criteria. What’s the most elegant way to right a stored procedure which accepts multiple parameters as search criteria but only returns the records that relate to the combination of search criteria and accommodates for nulls and empty strings as well.

The Constraints

• We want search criteria specified to match the record value in field
• We want empty string to match empty or null records
• We want null to not include the search criteria in the filter

The Setup

A very basic table to begin with (let’s call it TestingSqlSearchData for consistency):

IDName1Nick2John3Mary4NULL5Casey6

A Solution (hopefully not “the” solution)

declare @Name varchar(50) = null; --(or 'ni' or '')
	
select * from TestingSqlSearchData
where isnull(Name, '') LIKE 
	(case when @Name = '' then '' else '%' end) + 
	(case when @Name is null then isnull(Name, '') else @Name end) + 
	(case when @Name = '' then '' else '%' end);

This returns the following for each example:

@Name = null

IDName1Nick2John3Mary4NULL5Casey6

@Name = ‘ni’

IDName1Nick

@Name = ‘’

IDName4NULL6

The Challenge

The challenge is quite simple; we want to demonstrate more elegant solutions to this SQL scenario. You can use any SQL-based approach to accomplish this. If anyone wants to take up the challenge you can submit your solutions (complete with an explanation as to why it is more elegant — as this is not always apparent to everyone else) in the comments below.

P.S. — I really hope to see some comments, so don’t be shy! And, if no one comments, I’ll still leave the post up for at least one solution to this scenario. Who’s going to win bragging rights?