Re: search error

this one incorporates containstable which allows you to limit your
results set and you can order by rank.

CREATE PROCEDURE SearchSQL2 (@stringin varchar(200), @BooleanType int=
NULL)
AS
-- a @boolean type of null means a phrase based search
-- a @boolean type of 0 means a phrase based search
-- a @boolean type of 1 means an OR type search
-- a @boolean type of 2 means an AND type search
-- a @boolean type of 3 means an OR wildcarded type search
DECLARE @holdingString VarChar(2000)
DECLARE @whitespace INT
DECLARE @boolean VarChar(10)
--returning a syntax message if no search phrase is passed
IF LEN(@stringin)=0
BEGIN
PRINT 'usage is SimpleSQLFTSSearch ''Your Search Phrase goes here'''
RETURN -1
END
SET @boolean=case WHEN @booleantype=1 THEN char(34)+' OR ' + char(34)
WHEN @booleantype=2 THEN char(34)+' AND ' + char(34)
WHEN @booleantype=3 THEN char(34)+' OR ' + char(34)
ELSE ' ' END
DECLARE @counter INT
DECLARE @posold int
DECLARE @posnew int
SET @holdingstring='SELECT * FROM authors AS a JOIN
CONTAINSTABLE(authors,*,'''+char(34)
SELECT @whitespace=LEN(@stringin) - LEN(replace(@stringin,' ',''))
SELECT @posold=0
SELECT @posnew=Charindex(' ',@stringin)
WHILE @whitespace >=0
BEGIN
IF @whitespace=0
BEGIN
if @booleanType =3
begin
SELECT
@holdingString=@holdingString+SUBSTRING(@stringin,@posold+1,LEN(@stringin)-@posold+1)+'*'+char(34)+char(39)+',200)
AS t ON '
end
else
begin
SELECT
@holdingString=@holdingString+SUBSTRING(@stringin,@posold+1,LEN(@stringin)-@posold+1)+char(34)+char(39)+',200)
AS t ON '
end
--print @holdingString
END
ELSE
BEGIN
if @booleantype=3
begin
SELECT @holdingString = CASE WHEN LEN(SUBSTRING(@stringin,@posold+1,
@posnew-@posold-1))>0 THEN
@holdingString+SUBSTRING(@stringin,@posold+1,
@posnew-@posold-1)+'*'+@boolean ELSE @holdingstring END
SELECT @posold=@posnew, @posnew=Charindex(' ',@stringin, @posold+1)
END
else
begin
SELECT @holdingString = CASE WHEN LEN(SUBSTRING(@stringin,@posold+1,
@posnew-@posold-1))>0 THEN
@holdingString+SUBSTRING(@stringin,@posold+1,
@posnew-@posold-1)+@boolean ELSE @holdingstring END
SELECT @posold=@posnew, @posnew=Charindex(' ',@stringin, @posold+1)

end
end

SELECT @whitespace=@whitespace-1
END
SELECT @holdingString = @holdingString + 't.[KEY]=a.au_id ORDER BY
RANK DESC'
--PRINT @holdingstring
EXEC(@holdingstring)
RETURN @@rowcount

.

Relevant Pages

  • Re: How to optimize search on an indexed field
    ... Here is a proc I occasionally use which breaks the search phrase into ... -- a @boolean type of null means a phrase based search ... DECLARE @whitespace INT ... declare @rowcount int ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Need simple search string converted to full-text search string
    ... declare @NumberOfRoww int ... exec @NumberOfRows =SearchSQL1 'SearchPhrase' ... -- a @boolean type of null means a phrase based search ... declare @holdingString varchar ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Front end website full-text search
    ... -- a @boolean type of null means a phrase based search ... DECLARE @whitespace INT ... Looking for a SQL Server replication book? ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Google style search
    ... -- a @boolean type of null means a phrase based search ... DECLARE @whitespace INT ...
    (microsoft.public.sqlserver.fulltext)
  • Re: [patch 1/1] consolidate TRUE and FALSE
    ... being a boolean type in the kernel so you cannot use it in the core code. ... You mean typedef int bool... ... Linux NTFS maintainer / IRC: ...
    (Linux-Kernel)