Most efficient way of doing customer search stored proc

Question

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?

This is what i've come up with so far...

DECLARE @FirstName NVARCHAR(50) = '',
@LastName NVARCHAR(50) = '',
@City NVARCHAR(50) = '',
@Postcode NVARCHAR(10) = '',
@Country AS NVARCHAR(2) = '',
@Name AS NVARCHAR(50) = '',
@DateOfBirth AS NVARCHAR(12) = ''

SELECT TOP 100
    CU.FirstName ,
    CU.LastName ,
    CU.City ,
    CU.Postcode ,
    CO.Name ,
    CL.CompanyID ,
    COALESCE(CN.Country, 'Unknown') AS Country ,
    CU.CustomerID ,
    CASE WHEN CU.PrimaryCustomer = '1' THEN 'Yes'
         ELSE 'No'
    END AS [Primary] ,
    CU.MiddleName ,
    COALESCE(REPLACE(CONVERT(NVARCHAR, DateOfBirth, 103), '/', '-'),
             'Unknown') AS DateOfBirth ,
    CASE WHEN CU.Active = '1' THEN 'Active'
         WHEN CU.Active = '0' THEN 'Inactive'
         ELSE 'Unknown'
    END AS ActiveStatus
FROM    crm.dbo.Customer CU
    JOIN crm.dbo.CompanyLevel CL ON cl.CompanyLevelID = cu.CompanyLevelID
    JOIN crm.dbo.Company CO ON co.CompanyID = cl.CompanyID
    LEFT OUTER JOIN dbo.Country CN ON CN.CountryCode = CU.Country
WHERE   ( @FirstName = ''
          OR FirstName LIKE @FirstName + '%'
        )
        AND ( @LastName = ''
              OR LastName LIKE @LastName + '%'
            )
        AND ( @City = ''
              OR City LIKE @City + '%'
            )
        AND ( @PostCode = ''
              OR Postcode LIKE @PostCode + '%'
            )
        AND ( @Country = ''
              OR CU.Country LIKE @Country + '%'
            )
        AND ( @Name = ''
              OR CO.Name LIKE @Name + '%'
            )
        AND ( @DateOfBirth = ''
              OR REPLACE(CONVERT(NVARCHAR, DateOfBirth, 103), '/', '-') = @DateOfBirth
            )

Please help! I'm doing this to fix the current search which isn't working properly so is fairly urgent! Thanks.

Answer 1

@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

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

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

Answer 2

0

TBH I wouldn't try and do all this in one stored procedure.

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

answered Jun 28 '12 at 11:40 AM

Kev Riley ♦♦
46.1k ● 39 ● 43 ● 69

Hi Kev,

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

Jun 28 '12 at 11:47 AM jhowe

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.

Jun 28 '12 at 11:51 AM jhowe

if the format is dd-mm-yyyy and comes from a picker then there should be no option to search for a partial date then?

Jun 28 '12 at 11:52 AM Kev Riley ♦♦

and sorry - just reread the question - being able to cope with ANY or ALL fields - scrub the suggestion about a tailored sp!

Jun 28 '12 at 12:01 PM Kev Riley ♦♦

@Kev Riley Excellent advice. +1.

Jun 28 '12 at 12:19 PM Usman Butt

(comments are locked)

Most efficient way of doing customer search stored proc

Follow this question

Related Questions