DECLARE 
     @SearchTerm VARCHAR(255)
    ,@proximity VARCHAR(10)
    ,@tempstring NVARCHAR(4000)
    ,@ParsedSearchString NVARCHAR(4000)

--some example usage    
SET @SearchTerm = 'Find the best holiday cruise'
--SET @SearchTerm = 'Fun AND cruise'
--SET @SearchTerm = 'Fun OR cruise'
--SET @SearchTerm = 'Fun OR Pacific Cruise'
--SET @SearchTerm = 'C#, C++'    --you'll have to modify the replace below to allow the '+' signs
	
SET @SearchTerm = REPLACE(REPLACE(REPLACE(REPLACE(@SearchTerm,'%',''),'"',''),'+',''),'&','')
SET @ParsedSearchString = '1=1 '

IF CHARINDEX(' OR ',@SearchTerm) > 0
    SET @proximity = 'OR'
ELSE IF CHARINDEX(' AND ',@SearchTerm) > 0
    SET @proximity = 'AND'
ELSE
    SET @proximity = 'NEAR'


IF LTRIM(RTRIM(@SearchTerm)) <> '' 
    BEGIN

        EXEC dbo.FullTextParseSearchString 
            @SearchTerm
           ,@proximity
           ,@tempstring OUTPUT
           
        SET @ParsedSearchString = ISNULL(@ParsedSearchString,'') + ' OR (' + @tempstring + ')'

    END
    
SELECT @ParsedSearchString

CREATE PROCEDURE [dbo].[FullTextParseSearchString]
    (
     @keywords VARCHAR(500) = NULL
    ,@proximity VARCHAR(10) = 'NEAR'    -- and, or, near
    ,@parsedstring VARCHAR(500) OUTPUT 
    )
AS 
BEGIN

    SET CONCAT_NULL_YIELDS_NULL OFF 
    SET NOCOUNT ON

    DECLARE
        @sql VARCHAR(2000)
       ,@where VARCHAR(1000)
       ,@i INT                    -- old posn in string
       ,@j INT                    -- match posn in string
       ,@k INT                    -- new start posn
       ,@len INT                -- string length
       ,@search VARCHAR(500)
       ,@search2 VARCHAR(500)
       ,@fuzzy VARCHAR(1000)
       ,@keyword VARCHAR(500)
       ,@synonyms VARCHAR(255)
       ,@new_keywords VARCHAR(1000)
       ,@found INT

    SET @keywords = LTRIM(RTRIM(@keywords))

    SET @keywords = REPLACE(@keywords,'*',' ')
    SET @keywords = REPLACE(@keywords,' AND ',' ')
    SET @keywords = REPLACE(@keywords,' OR ',' ')
    SET @keywords = REPLACE(@keywords,'"',' ')
    SET @keywords = REPLACE(@keywords,'(',' ')
    SET @keywords = REPLACE(@keywords,')',' ')

    SET @keywords = REPLACE(@keywords,'o''','')

    -- mcdonald's ==> mcdonald''s, escape out the ' which is a valid piece of punctuation
    SET @keywords = REPLACE(@keywords,'''','''''')

    -- fix é type characters
    SET @found = CHARINDEX('é',@keywords)
    IF @found > 0 
        IF @found = LEN(@keywords) 
            SET @keywords = REPLACE(@keywords,'é','')
        ELSE 
            SET @keywords = REPLACE(@keywords,'é ','* ')

    -- set proximity type
    IF @proximity IS NULL
        OR @proximity = '' 
        SET @proximity = 'NEAR'

    SELECT
        @i = 1 -- start of the first string
    SELECT
        @j = 0 -- end of the first string unknown
    SELECT
        @len = LEN(@keywords)
    SELECT
        @search = ''
    SELECT
        @fuzzy = ''

    WHILE (@j <= @len) 
        BEGIN
            SELECT
                @k = @j + 1 -- New start posn

            -- cut up the string into its individual words, split this 
            -- into its inflectional part and standard FTI search portion
            SELECT
                @j = CHARINDEX(' ',@keywords,@k)

            -- Finish if no more found
            IF (@j <= 0) 
                BEGIN
                    -- Add the last part of the string
                    SELECT
                        @search = @search + '"'
                        + LTRIM(RTRIM(SUBSTRING(@keywords,@i,@len - @i + 1)))
                        + '*"'
                    SELECT
                        @search2 = @search2 + '"'
                        + LTRIM(RTRIM(SUBSTRING(@keywords,@i,@len - @i + 1)))
                        + '"'

                    IF NOT SUBSTRING(@keywords,@k,@len - @k + 1) 
                        IN ('of','and','at','a','the','&','it','for') 
                        SELECT
                            @fuzzy = @fuzzy
                            + 'FORMSOF(INFLECTIONAL,"'
                            + LTRIM(RTRIM(SUBSTRING(@keywords,@k,@len - @k + 1)))
                            + '")'
                    ELSE
                        SELECT
                            @fuzzy = @fuzzy + '1=1'
                                                    
                    BREAK
                END 

            IF (@j < @len) 
                BEGIN
                    IF (SUBSTRING(@keywords,@j - 1,1) <> ' ') 
                        BEGIN
                            SELECT
                                @search = @search + '"'
                                + LTRIM(RTRIM(SUBSTRING(@keywords,@i,@j - @i)))
                                + '*" ' + @proximity + ' '
                            SELECT
                                @search2 = @search2 + '"'
                                + LTRIM(RTRIM(SUBSTRING(@keywords,@i,@j - @i)))
                                + '" ' + @proximity + ' '

                            IF NOT SUBSTRING(@keywords,@i,@j - @i) 
                                IN ('of','and','at','a','the','&','it','for') 
                                SELECT
                                    @fuzzy = @fuzzy
                                    + 'FORMSOF(INFLECTIONAL,"'
                                    + LTRIM(RTRIM(SUBSTRING(@keywords,@i,@j - @i)))
                                    + '") AND '

                            SELECT
                                @i = @j + 1
                        END
                END
        END

    IF SUBSTRING(@fuzzy,LEN(@fuzzy) - 2,3) = ' OR' 
        SET @fuzzy = SUBSTRING(@fuzzy,1,LEN(@fuzzy) - 2)
    IF SUBSTRING(@fuzzy,LEN(@fuzzy) - 3,4) = ' AND' 
        SET @fuzzy = SUBSTRING(@fuzzy,1,LEN(@fuzzy) - 3)

    -- Example: ("mcdonalds") OR ("mcdonalds*") OR (FORMSOF(INFLECTIONAL,"mcdonalds"))

    SELECT
        @search = '"' + @keywords + '" OR ' + '(' + @search2
        + ') OR (' + @search + ') OR (' + @fuzzy + ')'

    SET @parsedstring = @search

    SET CONCAT_NULL_YIELDS_NULL ON

END

CREATE PROCEDURE [dbo].[FullTextSearchProducts]

     @SearchTerm NVARCHAR(4000)
    ,@MinRelevance INT
    ,@MaxRows INT
    ,@SilentExecution BIT = 0
    ,@NumRows INT = 0 OUTPUT

AS
BEGIN

    SET NOCOUNT ON

    SET @SearchTerm = ISNULL(@SearchTerm,'')
    SET @MinRelevance = ISNULL(@MinRelevance,10)
    SET @MaxRows = ISNULL(@MaxRows,500)
    
    DECLARE 
         @proximity VARCHAR(10)
        ,@tempstring NVARCHAR(4000)
        ,@ParsedSearchString NVARCHAR(4000)
        
    --add or delete any special characters as required    
    SET @SearchTerm = REPLACE(REPLACE(REPLACE(REPLACE(@SearchTerm,'%',''),'"',''),'+',''),'&','')
    
	SET @ParsedSearchString = '1=1 '

    IF CHARINDEX(' OR ',@SearchTerm) > 0
        SET @proximity = 'OR'
    ELSE IF CHARINDEX(' AND ',@SearchTerm) > 0
        SET @proximity = 'AND'
    ELSE
        SET @proximity = 'NEAR'


    IF LTRIM(RTRIM(@SearchTerm)) <> '' 
        BEGIN

            EXEC dbo.FullTextParseSearchString 
                @SearchTerm
               ,@proximity
               ,@tempstring OUTPUT
               
            SET @ParsedSearchString = ISNULL(@ParsedSearchString,'') + ' OR (' + @tempstring + ')'

        END


    IF OBJECT_ID('tempdb..#TempList') IS NOT NULL
    DROP TABLE #TempList

    SET ROWCOUNT @MaxRows

    SELECT DISTINCT
        p.ProdID
       ,p.ProdTitle
       ,p.ProdDesc
       ,p.ProdKeywords
       ,pr.[RANK] AS ProductRelevance
    INTO #TempList
    FROM
        dbo.vw_ProductSearch AS p
    INNER JOIN 
        CONTAINSTABLE
            (
            dbo.Product
            ,(*)
            ,@ParsedSearchString
            )
            AS pr
        ON p.ProdID = pr.[KEY]
    WHERE
        1=1
        AND pr.[RANK] > @MinRelevance 
    ORDER BY
         pr.[RANK] DESC
        ,p.ProdTitle
        

    IF @SilentExecution = 0
        BEGIN
            SELECT DISTINCT
                 ProdID
                ,ProdTitle
                ,ProductRelevance
            FROM
                #TempList
            ORDER BY
                ProductRelevance DESC
                ,ProdTitle
                
            SET @NumRows = @@ROWCOUNT
        END
    ELSE
        BEGIN
            SELECT @NumRows = COUNT(DISTINCT ProdID) FROM #TempList
        END

            
    SET ROWCOUNT 0

END

CREATE PROCEDURE [dbo].[SearchProducts]

     @ProdID INT
    ,@strSearchTerms NVARCHAR(MAX) = NULL
    
AS
BEGIN

    SET NOCOUNT ON
    
    DECLARE 
        @isSearch BIT

    IF OBJECT_ID('tempdb..#SearchResults') IS NOT NULL
    DROP TABLE #SearchResults
    
    CREATE TABLE #SearchResults (
        [ID] INT IDENTITY(1,1) NOT NULL,
        [ProdID] INT NULL,
        [ProdTitle] NVARCHAR(255) NULL,
        [ProductRelevance] INT NULL,
        PRIMARY KEY (ID))
        

    SET @strSearchTerms = NULLIF(@strSearchTerms,'')

    IF @strSearchTerms IS NOT NULL
        SET @isSearch = 1
    ELSE
        SET @isSearch = 0
        
    IF @isSearch = 1
        BEGIN

            /* Get the ProdIDs from the full-text search result */
    
            INSERT INTO #SearchResults
            EXEC dbo.FullTextSearchProducts @strSearchTerms, 0, 0, 0
  
        END
        

    SELECT DISTINCT
        p.ProdID
       ,p.ProdTitle
       ,p.ProdDesc
    FROM
       dbo.Product AS p
    WHERE
        1=1
        AND ((@isSearch = 1 AND p.ProdID IN
                (
                SELECT ProdID FROM #SearchResults WHERE id > 0
                )
            )
            OR
            (@isSearch = 0))                
    ORDER BY
        p.ProdID


END