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...
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...
Please help! I'm doing this to fix the current search which isn't working properly so is fairly urgent! Thanks.
@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)
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.
Assuming if any field is not meant to be searched, then the respective parameter would be NULL
Is it what you want
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