where clause if wildcards and parameters

I have a query like this:

declare @firstname varchar(30);
declare @lastname varchar(60);


select memid
     , firstname
     , lastname    
from Person


 if the user passes in first name I want to return that. if they pass in lastname I want it to return that.  it is possible that they might pass in first and lastname or just one of the two.  What would be the best way to write the where clause? It is also possible that they will pass in only part of the name.
vbnetcoderAsked:
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.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
<Total air code.  I do my own stunts too..>
declare @firstname varchar(30)
declare @lastname varchar(60)

-- One query 
select memid, firstname, lastname     
from Person
WHERE 
	(@firstname IS NOT NULL AND @lastname IS NULL AND firstname = @firstname) OR  
	(@firstname IS NULL AND @lastname IS NOT NULL AND lastname = @lastname) OR  
	(@firstname IS NOT NULL AND @lastname IS NOT NULL AND firstname = @firstname AND lastname = @lastname)

-- Multiple queries based on IF statement, guessing this will go much faster
IF @firstname IS NOT NULL AND @lastname IS NULL
	select memid, firstname, lastname     
	from Person
	WHERE firstname = @firstname 

ELSE IF @firstname IS NULL AND @lastname IS NOT NULL
	select memid, firstname, lastname     
	from Person
	WHERE lastname = @lastname

ELSE IF @firstname IS NOT NULL AND @lastname IS NOT NULL
	select memid, firstname, lastname     
	from Person
	WHERE firstname = @firstname AND lastname = @lastname

Open in new window

0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>also possible that they will pass in only part of the name.
That would mean using LIKE statements in the WHERE clause.  Is there another parameter that identifies if part vs. all of the string would be passed?  Otherwise you'll have some issues to deal with such as 'Smith' would return Smith, Smithson, Aerosmith, etc.
0
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
I would use this shorter WHERE clause, which is also more easy to expand:.
WHERE 
	(@firstname IS NULL or firstname = @firstname)
        AND
	(@lastname IS NULL or lastname = @lastname)

Open in new window

The equal operation can easily be changed to a LIKE. Assuming you would provide the starting part of a name, not an arbitrary part, a LIKE approach could look like
WHERE 
	(@firstname IS NULL or firstname like (@firstname + '%'))
        AND
	(@lastname IS NULL or lastname like (@lastname + '%'))

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
vbnetcoderAuthor Commented:
Qlemo what if the user enters something for the  first and last name?
0
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
That works too. If something is provided, the query checks for it. The IS NULL part is false for both conditions, and so the second part  - the real check -  is applied. If a var contains NULL, the first part is true, and the second part does not need to be checked.
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.