Classic firstname/lastname issue.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Classic firstname/lastname issue.

Author

Topic

craigwg
Posting Yak Master

154 Posts

Posted - 2011-06-17 : 12:14:21

I have a SP that works great as is. I'm having a problem with the next request. The form takes single variable that is a first and last name. As long the user enters both a first name and a last name with a space between this works great. But there comes a time when the user might enter only a first name or only a last name. I don't know if I should change the variables or change the way the variables are handled. I would love some input on this!

Here is the SP:


--ALTER PROCEDURE [dbo].[sel_GAB_nameSearch] (@fullname varchar(50)) AS
--DEBUG

DECLARE @FullName VARCHAR(100)
SET @FullName = 'Craig g'

-- MAIN
DECLARE @firstName varchar(100)
DECLARE @lastName varchar(100)

SET NOCOUNT ON

select @firstname = SUBSTRING(@FullName, 1, CHARINDEX(' ', @FullName) - 1),
       @lastname = SUBSTRING(@FullName, CHARINDEX(' ', @FullName) + 1, LEN(@FullName))


--select @firstname, @lastname
declare @sql varchar(1000)


	set @sql = 
	'
	select [name] = last_name + '', '' + first_name, employee_ident 
	from tpreports..vw_info_employee_ccms 
	where first_name like ''%'+@firstname+'%'' and last_name like ''%'+@lastname+'%'' 
	'

exec (@sql)

Craig Greenwood

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-06-17 : 12:21:06

I guess I would ask what the requirement is? There are lots of things you can do. But, if they don't meet the requirement(s), then it doesn't do much good for us to offer solutions.

craigwg
Posting Yak Master

154 Posts

Posted - 2011-06-17 : 12:24:40

The requirement is to make the SP return a valid result in the event that a user types in a single word (first name OR last name). For example if a user types "Craig" I would expect to see a list of all users who have either a first or a last name that is like %Craig%.

If the user enters a first and last name then I would expect to see a list of all users that have a first name like %firstname% and a last name like %lastname%.

I hope that helps.

Craig Greenwood

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-06-17 : 12:31:16

And what if the last name is "von Frankenstein" or "de la Hoya"?

Jim

Everyday I learn something that somebody else already knew

craigwg
Posting Yak Master

154 Posts

Posted - 2011-06-17 : 12:39:33

Considered. If you look closely at the way I'm dividing up the @full name I am taking the first name as the name up to the space and the last name up to the space. So in the event that a three name is entered, only the first and last values are considered. For example, "Frank Von Furstenbrand" becomes "Frank" and "Furstenbrand". Which is then easily found with the like statements. It may return more than one result (Frank Von Furstenbrand and Frank Van Furstenbrand) but this is acceptable in the application. And frankly if there are that many Furstenbrands warring over access to the application...I'd like a new job! jk

Craig Greenwood

craigwg
Posting Yak Master

154 Posts

Posted - 2011-06-17 : 12:44:15

This is actually really close but the problem comes with the selection of @firstname and @lastname. If a single name is entered there is no space and the substring function fails. Here is my approach:

--ALTER PROCEDURE [dbo].[sel_GAB_nameSearch] (@fullname varchar(50)) AS
--DEBUG

DECLARE @FullName VARCHAR(100)
SET @FullName = 'Craig'

-- MAIN
DECLARE @firstName varchar(100)
DECLARE @lastName varchar(100)

SET NOCOUNT ON

select @firstname = SUBSTRING(@FullName, 1, CHARINDEX(' ', @FullName) - 1),
       @lastname =  SUBSTRING(@FullName, CHARINDEX(' ', @FullName) + 1, LEN(@FullName))
 

declare @sql varchar(1000)

IF
@firstname='' or @lastname=''
BEGIN
	set @sql = 
	'
	select [name] = last_name + '', '' + first_name, employee_ident 
	from tpreports..vw_info_employee_ccms 
	where first_name like ''%'+@firstname+'%'' or last_name like ''%'+@lastname+'%'' order by [name]
	'
END
ELSE
BEGIN
	set @sql = 
	'
	select [name] = last_name + '', '' + first_name, employee_ident 
	from tpreports..vw_info_employee_ccms 
	where first_name like ''%'+@firstname+'%'' and last_name like ''%'+@lastname+'%'' order by [name]
	'
END
exec (@sql)

Craig Greenwood

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-06-17 : 13:08:39

That's pretty much what did

DECLARE @FullName VARCHAR(100)
SET @FullName = 'Craig'

DECLARE @sql varchar(1000)

-- MAIN
DECLARE @firstName varchar(100)
DECLARE @lastName varchar(100)

SET NOCOUNT ON

if charindex(' ',@FullName) >1
begin
select @firstname = SUBSTRING(@FullName, 1, CHARINDEX(' ', @FullName) - 1),
@lastname = SUBSTRING(@FullName, CHARINDEX(' ', @FullName) + 1, LEN(@FullName))

set @sql =
'
select [name] = last_name + '', '' + first_name, employee_ident
from tpreports..vw_info_employee_ccms
where first_name like ''%'+@firstname+'%'' and last_name like ''%'+@lastname+'%''
'
end
ELSE
set @sql =
'
select [name] = last_name + '', '' + first_name, employee_ident
from tpreports..vw_info_employee_ccms
where first_name like ''%'+@fullname+'%'' or last_name like ''%'+@fullname+'%''
'
select (@sql)

Jim

Everyday I learn something that somebody else already knew

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-06-17 : 13:11:13

Note, however, in each of our solutions, Oscar de la Hoya becomes "Oscar" and "de la hoya" .

Jim

Everyday I learn something that somebody else already knew

craigwg
Posting Yak Master

154 Posts

Posted - 2011-06-17 : 13:17:00

This is great. It's still frustrating to me that I can come so close. This site is great for giving me the little pushes over the edge I need from time to time.

Thank you, in other words!

Craig Greenwood

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources