Most efficient way of doing customer search stored proc

Update : i've pasted the code without the ISNULL which seems to be faster.

Update : i've come up with two different approaches for doing this i will edit my original code. Please let me know what is a better approach and if both are correct etc...

Hi all,

I've been tasked with creating a customer search stored proc that has the following fields.

FirstName
LastName
City
Postcode
Country
Name
DateOfBirth

Obviously for the user i would like them to search by ANY or ALL fields and have the wildcards bring back similar records i.e. search LastName Smith would bring back Smiths, Smithy, etc. No fields are mandatory. I'm struggling a bit with the DOB. The DOB field is datetime in the database how would i search for a partial DOB like 12/ or 2012/ for example?

@jhowe if you are looking to cope with ANY or ALL fields, then your procedure is not catering this scenario with all the ANDs in there?

As far as date search is concerned, if you are sure that the datepart would be dd-mm-yyyy, then there should also be assurance that the current stored format of the searched DOB column be dd-mm-yyyy as well. One option could be to search like

Another option could be (Does not seems efficient though but search for more records)

(
SUBSTRING(DateOfBirth,1,2) = SUBSTRING(@DateOfBirth, 1, 2)
OR -- COULD BE AND
SUBSTRING(DateOfBirth,1,2) = SUBSTRING(@DateOfBirth, 3, 2)
OR -- COULD BE AND
SUBSTRING(DateOfBirth,1,2) = SUBSTRING(@DateOfBirth, 9, 2)
)
OR
(
SUBSTRING(DateOfBirth,3,2) = SUBSTRING(@DateOfBirth, 1, 2)
OR -- COULD BE AND
SUBSTRING(DateOfBirth,3,2) = SUBSTRING(@DateOfBirth, 3, 2)
OR -- COULD BE AND
SUBSTRING(DateOfBirth,3,2) = SUBSTRING(@DateOfBirth, 9, 2)
)

And same for the year part.

But its all guesswork. You need to sit down with the stake holders and should come up with what could be the scenarios and how they need to be dealt with.

EDIT:

Assuming if any field is not meant to be searched, then the respective parameter would be NULL

WHERE ( ISNULL(CU.FirstName, '') LIKE ( @FirstName + '%' ) OR @FirstName IS NULL)
AND (ISNULL(CU.LastName, '') LIKE ( @LastName + '%' ) OR @LastName IS NULL)
AND (ISNULL(CU.City, '') LIKE ( @City + '%' ) OR @City IS NULL)
AND (ISNULL(CU.Postcode, '') LIKE ( @Postcode + '%' ) OR Postcode IS NULL)
AND (ISNULL(CU.Country, '') LIKE ( @Country + '%' ) OR @Country IS NULL)
AND (ISNULL(CO.Name, '') LIKE ( @Name + '%' ) OR @Name IS NULL)
AND (ISNULL(CONVERT(NVARCHAR, DateOfBirth, 110), '') = (@dateofbirth) OR @dateofbirth IS NULL)

EDIT1:

Is it what you want

(
(
@FirstName IS NULL
OR FirstName LIKE @FirstName + '%'
)
OR (
FirstName IS NULL
AND @FirstName IS NOT NULL
)
)
AND (
(
@LastName IS NULL
OR LastName LIKE @LastName + '%'
)
OR (
LastName IS NULL
AND @LastName IS NOT NULL
)
)
AND (
(
@City IS NULL
OR City LIKE @City + '%'
)
OR (
City IS NULL
AND @City IS NOT NULL
)
)
AND (
(
@PostCode IS NULL
OR Postcode LIKE @PostCode + '%'
)
OR (
PostCode IS NULL
AND @Postcode IS NOT NULL
)
)
AND (
(
@Country IS NULL
OR Country LIKE @Country + '%'
)
OR (
Country IS NULL
AND @Country IS NOT NULL
)
)
AND (
(
@Name IS NULL
OR Name LIKE @Name + '%'
)
OR (
Name IS NULL
AND @Name IS NOT NULL
)
)
AND (
(
@DateOfBirth IS NULL
OR REPLACE(CONVERT(NVARCHAR, DateOfBirth, 103), '/', '-') = @DateOfBirth
)
OR (
DateOfBirth IS NULL
AND @DateOfBirth IS NOT NULL
)
)

You're right USMAN. As the DOB is in a fixed format i have updated my question with the correct format the app needs. It is 110 style. You're also correct about AND not working. Replacing AND with OR doesn't work either. I need some sort of combination of the both? The app also has validation on it so that the full date in that format MUST be entered.

Can you push back on the application development team and see if they 'know' at search time what field is being passed through - that way you can tailor a sp for each 'type' of search. (This will also alleviate any bad plan issues cause by parameter sniffing)

Then for DoB searches, the UI can give a date picker (with wildcards) instead of a string input. You would then have a segmented date coming through and can easily search using datepart()

Unfortunately the app is outsourced and changes have all been released etc. so i just have to make it work. The database is only a few gig, couple million records so it doesn't have to be massively efficient/complicated...

I've just found out that the DOB search input field has to be in format dd-mm-yyyy and it has a calendar picker... so that might make it a bit easier... They also have the option to manually enter a date.