trying to build AND search similar to GRAZ article ITEMID 1876

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

SQL Server 2000 Forums

SQL Server Development (2000)

trying to build AND search similar to GRAZ article ITEMID 1876

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-07-10 : 09:04:10

Charles writes "Graz article on keyword search was wonderful (and worked) but I really need an AND search. I modified the code by building a where clause, and moving the insert statement from ionside the while loop to outside. However instead of returning the correct data (or SQL error message) it just sort of hangs until I cancel the query).

Would appreciate any comments or suggestions. Stumped about four or five SQL developers here in the City of Philadelphia Government on this one.

Thank you very much

Below is my Stored Procedure------------------------------
CREATE PROCEDURE pro_programsearch
@progname varchar(1000)

AS
--based on sqlteam.com, graz article 1876 on keywords
set nocount on
--@progname is the array to parse, @separator is the parsing character
--default value
declare @separator_position int --locates separator position
declare @progname_value varchar(1000)
declare @like_text varchar(1000)
declare @separator char(1)
set @separator=','
--print 'Hi There'
begin
if @progname is Null select @progname=''
end
--clear out uneeded words
set @progname=replace(@progname,'the ','')

--add separator at the end
set @progname=@progname+','
--add
--build temporary table to clear the results
create table #searchResults (itemID int)

--now loop through the array and find the damned words
declare @loopcount int
set @loopcount=1

while patIndex('%'+@separator+'%',@progname) <>0
declare @sqlsearch varchar(1000)
begin
--as we know, patindex matches pattern against a string, silly (not silly string).

select @separator_position = patindex('%'+@separator+'%',@progname)
select @progname_value=left(@progname,@separator_position-1)
--build selection criteria
if ( @loopcount=1)begin
select @like_text='%'+@progname_value+'%'
end --end if
else begin
select @like_text=@like_text+' AND service like '+'%'+@progname_value+'%'
end --end else

--now replace what we processed with an empty string
select @progname=stuff(@progname,1,@separator_position,'')
select @loopcount=@loopcount+1
end --end while

--insert id into results
select @sqlsearch='insert #searchresults select id from services where service like ' +@like_text

exec(@sqlsearch)

--declare sql statement
declare @sql varchar(2500)
--build your sql statement

SELECT @sql= 'SELECT s.Service, sl.orgName, sl.streetnum, sl.Address1, sl.Address2, sl.City, sl.State, sl.Zip, sl.ID AS serviceLocationID, s.id AS serviceID
FROM dbo.Services s INNER JOIN
dbo.serviceLocation sl ON s.serviceLocationID = sl.ID INNER JOIN
#searchresults ON s.id = #searchresults.itemid'

execute (@sql)
set nocount off
GO"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources