x

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?

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.

more ▼

asked Jun 28 '12 at 11:23 AM in Default

jhowe gravatar image

jhowe
1.1k 47 55 60

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

@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
                 )
)
more ▼

answered Jun 28 '12 at 12:30 PM

Usman Butt gravatar image

Usman Butt
13.9k 6 8 14

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.
Jun 28 '12 at 01:38 PM jhowe
@jhowe I have edited my answer for AND/OR thing.
Jun 28 '12 at 02:09 PM Usman Butt

Hi Usman,

I've posted two different queries can you let me know which one is best?
Jun 28 '12 at 04:00 PM jhowe

@jhowe That you have to test as it would depend. But I do not like COALESCE unless it is inevitable. Use ISNULL instead, that could be much faster.

For e.g.

CU.FirstName LIKE ISNULL(@FirstName + '%', CU.FirstName)
Hope it helps.
Jun 28 '12 at 04:11 PM Usman Butt
@Usman i have posted my finished code. Please let me know if there is any reason why i should use one or the other technically...
Jun 28 '12 at 04:42 PM jhowe
(comments are locked)
10|1200 characters needed characters left

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

answered Jun 28 '12 at 11:40 AM

Kev Riley gravatar image

Kev Riley ♦♦
50.7k 43 49 76

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)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1816
x562

asked: Jun 28 '12 at 11:23 AM

Seen: 811 times

Last Updated: Jun 29 '12 at 02:31 PM