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?
 
QlemoBatchelor, 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
 
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
 
vbnetcoderAuthor Commented:
Qlemo what if the user enters something for the  first and last name?
0
 
QlemoBatchelor, 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
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.