Searchinh name 'Fullname'

I have this stored procedure that can search for either first name or last name.
However,'I would like to modify it so it can also search if the fullname is entered - that is first and Last name or last and first name- for example: Joe Smith or Smith, Joe.
Is that possible? Maybe there is a way to do it but a lot of work involved.
 thanks
See below..
CREATE PROCEDURE sp_GetPersonName
@pname    varchar(25),
@typename    char(2)    
AS
if @typename = 'FN'
BEGIN
select  *
from VW_PERSON
where UPPER(RTRIM(FIRSTNAME)) like (@pname + '%')
order by LASTNAME, FIRSTNAME
END
ELSE
BEGIN
select  *
from VW_PERSON
where UPPER(RTRIM(LASTNAME)) like (@pname + '%')
order by LASTNAME, FIRSTNAME
END
GO
zachvaldezAsked:
Who is Participating?
 
SharathData EngineerCommented:
try this
CREATE PROCEDURE sp_GetPersonName
@pname    varchar(25),
@typename    char(2)    
AS
if @typename = 'FN' 
BEGIN
select  *
from VW_PERSON
where UPPER(RTRIM(FIRSTNAME)) like (@pname + '%')
order by LASTNAME, FIRSTNAME
END
ELSE IF @typename = 'LN' 
BEGIN
select  *
from VW_PERSON
where UPPER(RTRIM(LASTNAME)) like (@pname + '%') 
order by LASTNAME, FIRSTNAME
END
ELSE --fullname 

select  *
from VW_PERSON
where UPPER(RTRIM(FIRSTNAME)) like (@pname + '%') 
 or UPPER(RTRIM(LASTNAME)) like (@pname + '%') 
go

Open in new window

0
 
Aneesh RetnakaranDatabase AdministratorCommented:

CREATE PROCEDURE sp_GetPersonName
@pname    varchar(25),
@typename    char(2)    
AS
if @typename = 'FN'
BEGIN
select  *
from VW_PERSON
where UPPER(RTRIM(FIRSTNAME)) like (@pname + '%')
order by LASTNAME, FIRSTNAME
END
ELSE IF @typename = 'LN'
BEGIN
select  *
from VW_PERSON
where UPPER(RTRIM(LASTNAME)) like (@pname + '%')
order by LASTNAME, FIRSTNAME
END
ELSE --fullname

select  *
from VW_PERSON
where isnull(lastName,'')+isnull(FirstName,'') = @pname
 or isnull(FirstName,'')+isnull(lastName,'') = @pname


GO
0
 
zachvaldezAuthor Commented:
It's not returning anything...
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
SharathData EngineerCommented:
hmmm.... included a blank space between first and last names and added trim functions in Anessh's query.

How are you passing pname to SP in case of full name.
CREATE PROCEDURE sp_GetPersonName
@pname    varchar(25),
@typename    char(2)    
AS
if @typename = 'FN' 
BEGIN
select  *
from VW_PERSON
where UPPER(RTRIM(FIRSTNAME)) like (@pname + '%')
order by LASTNAME, FIRSTNAME
END
ELSE IF @typename = 'LN' 
BEGIN
select  *
from VW_PERSON
where UPPER(RTRIM(LASTNAME)) like (@pname + '%') 
order by LASTNAME, FIRSTNAME
END
ELSE --fullname 

select  *
from VW_PERSON
where ltrim(rtrim(isnull(lastName,'')+' '+isnull(FirstName,''))) = @pname 
 or ltrim(rtrim(isnull(FirstName,'')+' '+isnull(lastName,''))) = @pname 
GO

Open in new window

0
 
zachvaldezAuthor Commented:
It worked! But in reality, user may not completely put in the whole name if unsure so he may enter..
Joe Smi which may be Joe Smith or Joe Smithe..
so how can it take that requirement. would using Like instead of = will do it?

thanks
0
 
SharathData EngineerCommented:
try like this.
CREATE PROCEDURE sp_GetPersonName
@pname    varchar(25),
@typename    char(2)    
AS
if @typename = 'FN' 
BEGIN
select  *
from VW_PERSON
where UPPER(RTRIM(FIRSTNAME)) like (@pname + '%')
order by LASTNAME, FIRSTNAME
END
ELSE IF @typename = 'LN' 
BEGIN
select  *
from VW_PERSON
where UPPER(RTRIM(LASTNAME)) like (@pname + '%') 
order by LASTNAME, FIRSTNAME
END
ELSE --fullname 

select  *
from VW_PERSON
where isnull(FirstName,'')+ ' '+isnull(LastName,'') like isnull(replace(@pName,' ','%'),'')+'%'
    or isnull(LastName,'')+ ' '+isnull(FirstName,'') like isnull(replace(@pName,' ','%'),'')+'%'
GO

Open in new window

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.