if ( @keywords is not null )
begin
declare @str varchar(4000)
declare @word varchar(150)
declare @strlen int
declare @bit bit
declare @comma bit
declare @index int
create table #words ( word varchar(150) )
create table #word_users ( id int )
set @str = @keywords
set @strlen = len(@str)
set @bit = 0
set @index = patindex('%,%', @str)
if ( @index = 0 )
begin
set @comma = 1
set @index = patindex('% %', @str)
end else begin
set @comma = 0
end
while ( @index > 0 )
begin
set @word = substring(@str, 1, @index - 1)
insert #words values ( @word )
if ( @bit = 0 )
begin
insert into #word_users
select distinct _user.id from _user
left join _order_ship on _order_ship.idUser = _user.id
left join _order_bill on _order_bill.idUser = _user.id
left join _country on _country.id = _user.idCountry
left join _states on _states.id = _user.idState
where
_user.fname like '%'+ @word +'%'
or _user.lname like '%'+ @word +'%'
or _user.address like '%'+ @word +'%'
or _user.city like '%'+ @word +'%'
or _user.zip like '%'+ @word +'%'
or _user.email like '%'+ @word +'%'
or _user.notes like '%'+ @word +'%'
or _user.phone like '%'+ @word +'%'
or _order_bill.phone like '%'+ @word +'%'
or _order_ship.phone like '%'+ @word +'%'
or _country.name like '%'+ @word +'%'
or _country.abrev like '%'+ @word +'%'
or _states.name like '%'+ @word +'%'
or _states.abrev like '%'+ @word +'%'
set @bit = 1
end else begin
delete from #word_users where id not in (
select distinct _user.id from _user
left join _order_ship on _order_ship.idUser = _user.id
left join _order_bill on _order_bill.idUser = _user.id
left join _country on _country.id = _user.idCountry
left join _states on _states.id = _user.idState
where
_user.fname like '%'+ @word +'%'
or _user.lname like '%'+ @word +'%'
or _user.address like '%'+ @word +'%'
or _user.city like '%'+ @word +'%'
or _user.zip like '%'+ @word +'%'
or _user.email like '%'+ @word +'%'
or _user.notes like '%'+ @word +'%'
or _user.phone like '%'+ @word +'%'
or _order_bill.phone like '%'+ @word +'%'
or _order_ship.phone like '%'+ @word +'%'
or _country.name like '%'+ @word +'%'
or _country.abrev like '%'+ @word +'%'
or _states.name like '%'+ @word +'%'
or _states.abrev like '%'+ @word +'%'
)
end
set @str = substring(@str, @index + 1, @strlen)
set @strlen = len(@str)
if ( @comma = 1 )
begin
set @index = patindex('% %', @str)
end else begin
set @index = patindex('%,%', @str)
if ( @index = 0 )
begin
set @comma = 1
set @index = patindex('% %', @str)
end
end
end