asked on

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

ASKER CERTIFIED SOLUTION

Shaun Kline

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Scott Pletcher

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 + '%'))

Michael Sterling

ASKER

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.

Scott Pletcher

Yeah, I get that. But what does that mean for how you want the search to work?

Michael Sterling

ASKER

If it is null then I only want the search to use the value that isn't null when filtering.

Scott Pletcher

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

Michael Sterling

ASKER

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.

Scott Pletcher

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.

Michael Sterling

ASKER

Yes he did, thanks. Side note, I don't think you're an idiot, a bit abrasive, but not an idiot.

Mark Wills

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

Mark Wills

Shaun's post was acknowledged by Asker

Mark Wills

Cancel Delete

Michael Sterling

ASKER

Thanks for your help. I've been out of the country for a bit. Sorry about the delay.