How do I filter my SQL stored proc to account for a null parameter?

I need to alter an existing SQL stored proc to search for the possibility that the user entered a first name, last initial OR last name first initial string. So basically the string will have a space in it something like: "Firstname L" OR "Lastname F". It currently searches and filters for just a solid string (no blank/white spaces), which it takes through one parameter. I'm now adding another parameter to the string that could be null, but if its not, I need to change my filter accordingly. How can I do this?

ALTER procedure [dbo].[MyStoredProcedure]
(
 @ResultLimit INT,
 @MachID INT,
 @SiteID INT,
 @SearchAllSites BIT,
 @SearchText VARCHAR(200),
 @SearchTextII VARCHAR(200) <---MY NEW PARAMETER THAT COULD/CAN BE NULL
)
.
.
.
SELECT BLAH BLAH BLAH
.
.
.
FROM BLAH BLAH BLAH
.
.
.
WHERE  
     ((@SearchText IS NULL OR d.FIRSTNAME Like @SearchText + '%')
     OR
     (@SearchText IS NULL OR d.LASTNAME Like @SearchText + '%'))
//HOW DO CHANGE MY FILTER TO ACCOUNT FOR A POSSIBLE NULL VALUE AND SEARCH FOR THE OTHER PART OF THE NAME???

Open in new window

LVL 1
Michael SterlingWeb Applications DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Shaun KlineLead Software EngineerCommented:
Try this:
     ( ( (@SearchText IS NULL OR d.FIRSTNAME Like @SearchText + '%') AND (@SearchTextII IS NULL OR d.LastName Like @SearchTextII + '%') )
     OR
       ( (@SearchText IS NULL OR d.LASTNAME Like @SearchText + '%') AND (@SearchTextII IS NULL OR d.FirstName LIKE @SearchTextII + '%') ) )

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior DBACommented:
Difficult to be sure exactly what you mean, but if the second string being present should cause different searches, then this may be what you want:

@SearchTextII VARCHAR(200) = NULL <---MY NEW PARAMETER THAT COULD/CAN BE NULL
)
.
.
.
WHERE  
     (@SearchTextII IS NULL AND (d.FIRSTNAME Like @SearchText + '%' OR
                                 d.LASTNAME Like @SearchText + '%'))
     OR
     (@SearchTextII IS NOT NULL AND (d.FIRSTNAME Like @SearchText + '%' AND
                                     d.LASTNAME Like @SearchTextII + '%'))
0
Michael SterlingWeb Applications DeveloperAuthor Commented:
In my C# i'm scrubbing the input string and assigning a value of either null or a value to what will eventually by absorbed by @SearchTextII. So it will be null or something.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Scott PletcherSenior DBACommented:
Yeah, I get that.  But what does that mean for how you want the search to work?
0
Michael SterlingWeb Applications DeveloperAuthor Commented:
If it is null then I only want the search to use the value that isn't null when filtering.
0
Scott PletcherSenior DBACommented:
Yeah, I get that.  I'm not an idiot.

But you are searching two different columns.  Does 2 input values mean you search both columns for both values?  Or each column for only one value?  I.e., some clear search rules like:

If only search value1 is specific, search first name OR last name for it,
if both search values are specified, then search ...
0
Michael SterlingWeb Applications DeveloperAuthor Commented:
Haha,..yes, 2 input values means search both first and last name columns for both values. So inputting "John S" would/should return John Smith but also should return Shirley John.
0
Scott PletcherSenior DBACommented:
Then it looks like Shaun gave you exactly what you need at the start.  I figured you must have meant something else since you didn't comment on his answer.
0
Michael SterlingWeb Applications DeveloperAuthor Commented:
Yes he did, thanks. Side note, I don't think you're an idiot, a bit abrasive, but not an idiot.
0
Mark WillsTopic AdvisorCommented:
>> "John S" would/should return John Smith but also should return Shirley John.

Dont think you will get the Shirley John result from "John S%" unless you are breaking that into "John" and "S"

Will work if you entered "John" as one param and "S" as another, but you would also get all the Johnny Smiths, Johnathan, John-Paul, Johnray, etc - there is over 200 names starting with John

Would have thought you might want to first check exact match before wildcarding each parameter...

Let us know if that is a consideration.
0
Mark WillsTopic AdvisorCommented:
Shaun's post was acknowledged by Asker
0
Mark WillsTopic AdvisorCommented:
Cancel Delete
0
Michael SterlingWeb Applications DeveloperAuthor Commented:
Thanks for your help. I've been out of the country for a bit. Sorry about the delay.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.