where clause within a while loop not working 1

[rokitsalad]

I have a stored procedure which searches within a view to find values containing any of the words passed in a searchstring. I have performed extensive debugging on this and I have pinpointed the problem to the where clauses of the select into statement within the loop. For some reason, no matter what the where clause is, if it is present, no values are selected. Here's the code for the sp:

ALTER PROCEDURE [dbo].[searchSiteEmployees]
(
@str varchar(500)
)
AS

/*
CREATED FOR: ORDER MANAGER

ACCEPTS A STRING OF UP TO 500 CHARACTERS. FOR EACH WORD IN THE STRING, THIS CHECKS TO SEE IF THERE IS A SITE EMPLOYEE WITH A SIMILAR VALUE
FOR ANY OF:
EMPLOYEE_NUMBER
FST_FORENAME
SURNAME
KNOWN_AS_NAME
JOB_TITLE

CREATES A TEMPORARY TABLE IN R2_CYBLIVE_DM CALLED TEMP_EMPLOYEESEARCH AND DELETES IT AFTERWARDS

POSSIBLE PROBLEMS MAY OCCUR IF THE TEMP TABLE DOESN'T GET DETETED. THIS MAY HAPPEN WITH MULTIPLE USERS. TRANSACTION LEVEL MAY NEED INCREASING.
*/

begin transaction dosearch

declare @tempstr varchar(500)
declare @strvalue varchar(50)
declare @endpoint int

create table temp_employeesearch (
ref int primary key identity,
employee_number char(10),
fst_forename varchar(50),
surname varchar(50),
known_as_name varchar(50),
job_title varchar(100)
)

select @tempstr=@str


while charindex(' ',@tempstr)!=0
begin
select @endpoint=charindex(' ',@tempstr)
select @strvalue = convert(varchar(50),substring(@tempstr,1,@endpoint))
select @tempstr = right(@tempstr,len(@tempstr)-1-len(@strvalue))

--debug
select @strvalue nametosearch, len(@strvalue) length
select @tempstr remainingnames, len(@tempstr) length
--end debug

insert into temp_employeesearch (temp_employeesearch.employee_number, temp_employeesearch.fst_forename, temp_employeesearch.surname, temp_employeesearch.known_as_name, temp_employeesearch.job_title)
select vw_ordermanager_sitestaff.employee_number, vw_ordermanager_sitestaff.fst_forename, vw_ordermanager_sitestaff.surname, vw_ordermanager_sitestaff.known_as_name, vw_ordermanager_sitestaff.job_title
from vw_ordermanager_sitestaff
where vw_ordermanager_sitestaff.employee_number like '%'+@strvalue+'%'
or vw_ordermanager_sitestaff.fst_forename like '%'+@strvalue+'%'
or vw_ordermanager_sitestaff.surname like '%'+@strvalue+'%'
or vw_ordermanager_sitestaff.known_as_name like '%'+@strvalue+'%'
or vw_ordermanager_sitestaff.job_title like '%'+@strvalue+'%'

--debug
select vw_ordermanager_sitestaff.employee_number, vw_ordermanager_sitestaff.fst_forename, vw_ordermanager_sitestaff.surname, vw_ordermanager_sitestaff.known_as_name, vw_ordermanager_sitestaff.job_title
from vw_ordermanager_sitestaff
where vw_ordermanager_sitestaff.employee_number like '%'+@strvalue+'%'
or vw_ordermanager_sitestaff.fst_forename like '%'+@strvalue+'%'
or vw_ordermanager_sitestaff.surname like '%'+@strvalue+'%'
or vw_ordermanager_sitestaff.known_as_name like '%'+@strvalue+'%'
or vw_ordermanager_sitestaff.job_title like '%'+@strvalue+'%'
--end debug
end

--debug
select @tempstr finalname, len(@tempstr) length
-- end debug

insert into temp_employeesearch (temp_employeesearch.employee_number, temp_employeesearch.fst_forename, temp_employeesearch.surname, temp_employeesearch.known_as_name, temp_employeesearch.job_title)
select vw_ordermanager_sitestaff.employee_number, vw_ordermanager_sitestaff.fst_forename, vw_ordermanager_sitestaff.surname, vw_ordermanager_sitestaff.known_as_name, vw_ordermanager_sitestaff.job_title
from vw_ordermanager_sitestaff
where vw_ordermanager_sitestaff.employee_number like '%'+@tempstr+'%'
or vw_ordermanager_sitestaff.fst_forename like '%'+@tempstr+'%'
or vw_ordermanager_sitestaff.surname like '%'+@tempstr+'%'
or vw_ordermanager_sitestaff.known_as_name like '%'+@tempstr+'%'
or vw_ordermanager_sitestaff.job_title like '%'+@tempstr+'%'


-- debug
select employee_number, fst_forename, surname, known_as_name, job_title
from vw_ordermanager_sitestaff
where vw_ordermanager_sitestaff.employee_number like '%'+@tempstr+'%'
or vw_ordermanager_sitestaff.fst_forename like '%'+@tempstr+'%'
or vw_ordermanager_sitestaff.surname like '%'+@tempstr+'%'
or vw_ordermanager_sitestaff.known_as_name like '%'+@tempstr+'%'
or vw_ordermanager_sitestaff.job_title like '%'+@tempstr+'%'
-- end debug

-- output dataset
select employee_number, fst_forename, surname, known_as_name, job_title
from temp_employeesearch
order by surname, fst_forename

drop table temp_employeesearch

commit transaction dosearch
GO

There are some debug blocks which are marked. The debug select block within the while loop also does not return any values when the while clause is left in.

The purpose of the sp is to create a temporary table, then loop through all but the last word in the searchstring while entering selected values matching each word into the temporary table. Then the last word is searched on and the result set entered.

The last word always returns data into the table, but there is nothing inserted or selected for debug during the loops.

I've been messing with this all day but I can't make the where clause work.

I'd be most greatful if someon could shed some light on this.

 

[vongrunt]

Here is parsing code:

declare @str varchar(500)
set @str = 'blah blah2 foo bar'

declare @tempstr varchar(500)
declare @strvalue varchar(50)
declare @endpoint int

select @tempstr=@str

while charindex(' ',@tempstr)!=0
begin
	select @endpoint=charindex(' ',@tempstr)
    select @strvalue = convert(varchar(50),substring(@tempstr,1,@endpoint))
    select @tempstr = right(@tempstr,len(@tempstr)-1-len(@strvalue))
	select '%' + @strvalue + '%', @endpoint, len(@strvalue),  @strvalue, @tempstr
end

Extra trailing space in @strvalue...

There are some other possible problems as well... let's fix parsing first.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[rokitsalad]

BUGGER!

Here I was thinking that a len would return the total character length of the field INCLUDING any trailing spaces!

Thanks, vongrunt - I didn't have much hair to begin with but I've pulled half of what's there out since I began that ;o)

Anyway - yes, there is a trailing space in each word.

Thanks very much.