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):

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?

One thought on “The Stored Procedure for Searching Challenge

  1. 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.

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s