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):
| ID | Name |
| 1 | Nick |
| 2 | John |
| 3 | Mary |
| 4 | NULL |
| 5 | Casey |
| 6 |
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
| ID | Name |
| 1 | Nick |
| 2 | John |
| 3 | Mary |
| 4 | NULL |
| 5 | Casey |
| 6 |
@Name = ‘ni’
| ID | Name |
| 1 | Nick |
@Name = ”
| ID | Name |
| 4 | NULL |
| 6 |
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?
Nicholas Barger's Blog 
I had to read and reread your requirements before it fully sunk in but I think ANOTHER (key point being another – not sure if it’s more elegant) way of doing this (especially if you are using a stored procedure) is as follows:
CREATE PROCEDURE cp_SearchMyData
(
@pSearchCriteria VARCHAR(50) = NULL
)
AS
IF @pSearchCriteria IS NULL
BEGIN
SELECT
[Name]
FROM [dbo].[TestingSqlSearchData]
— return all records regardless
END ELSE
BEGIN
SELECT
[Name]
FROM [dbo].[TestingSqlSearchData]
WHERE [Name] LIKE ‘%’ + @pSearchCriteria + ‘%’
END
RETURN
OK – now for my explanation:
1. By writing the proc this way you are making the requirements of your where clause much easier to read.
2. You are predefining the NULL condition input and not searching for it.
3. Its easier to read because humans like to if..then..else!
4. You are specifying the fields you want in the select statement and that makes the query more efficient in the event that there 300 fields in the table (I know there’s not but you said to explain!).
One note: Your original where clause is built using the % + criteria + %. If you had a new name “Anita” for example – she would return as part of your where clause. Is this what you would want OR do you want only those names that begin with ‘ni’. I couldn’t tell by the directions so I left it as is but you would simple lop off the prefix ‘%’ sign to make it work to only return ‘Nick’.
What your post really insinuates is not how best to do something but more: “Do you know what your data is?” I fight this all day long.
My 2 cents.