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?
 
Shaun KlineConnect With a Mentor Lead 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
 
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
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.