Link to home
Start Free TrialLog in
Avatar of Russ Torlage
Russ Torlage

asked on

MSSQL Function

I am trying to create a function, but Intellesense keeps telling me there are errors. Can you help me to fix these errors?
The issue is in this section: SELECT display_term FROM sys.dm_fts_parser(N'FORMSOF(FREETEXT, "' + @searchTerm + '")', 1033, 0, 1)

For some reason Intellesense  does not like the @searchTerm variable.

This code is from a sample code in a blog:

CREATE FUNCTION [dbo].[HighLightSearch](@contents NVARCHAR(MAX),
  @searchTerm NVARCHAR(4000), @style NVARCHAR(4000), @maxLen INT)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @c NCHAR(1)
    DECLARE @len INT = 0
    DECLARE @l INT = 0
    DECLARE @p INT = 0
    DECLARE @prevPos INT = 0
    DECLARE @margin INT
    DECLARE @term NVARCHAR(4000)
    DECLARE @retval NVARCHAR(MAX) = ''
   
    DECLARE @positions TABLE
    (
        S INT,
        L INT
    )

    -- find all occurrences of the search term

    DECLARE cur1 CURSOR FOR
    SELECT display_term FROM sys.dm_fts_parser(N'FORMSOF(FREETEXT, "' + @searchTerm + '")', 1033, 0, 1)
   
    OPEN cur1
    FETCH NEXT FROM cur1 INTO @term

    WHILE @@FETCH_STATUS = 0
    BEGIN
        WHILE 1 = 1
        BEGIN
            SET @p = CHARINDEX(@term, @contents, @p)
            IF @p <= 0 BREAK
           
            SET @l = LEN(@term)
           
            IF @p > 0 BEGIN
                SET @c = SUBSTRING(@contents, @p - 1, 1)
                IF @c <> ' ' AND @c <> NCHAR(9) AND
                   @c <> NCHAR(13) AND @c <> NCHAR(10) BREAK
            END
           
            INSERT INTO @positions (S, L) VALUES(@p, @l)
            SET @p = @p + LEN(@term)
        END
       
        FETCH NEXT FROM cur1 INTO @term  
    END  

    CLOSE cur1
    DEALLOCATE cur1
   
    -- build the result string
   
    DECLARE cur2 CURSOR FOR
    SELECT S, MAX(L)
    FROM @positions
    GROUP BY S
    ORDER BY S
   
    SET @margin = LOG(@maxLen) * 5
    IF @margin > @maxLen / 4 SET @margin = @maxLen / 4
    SELECT @prevPos = MIN(S) - @margin FROM @positions

    OPEN cur2
    FETCH NEXT FROM cur2 INTO @p, @l

    WHILE @@FETCH_STATUS = 0 AND @len < @maxLen
    BEGIN
        SET @retval = @retval + SUBSTRING(@contents, @prevPos, @p - @prevPos)
        SET @retval = @retval + '<span style="' + @style + '">' + SUBSTRING(@contents, @p, @l)  + '</span>'
        SET @len = @len + @p - @prevPos + @l
        SET @prevPos = @p + @l
       
        FETCH NEXT FROM cur2 INTO @p, @l
    END  

    CLOSE cur2
    DEALLOCATE cur2

    SET @margin = LOG(@maxLen) * 5
    IF @margin + @len < @maxLen SET @margin = @maxLen - @len
    IF @margin > 0 SET @retval = @retval + SUBSTRING(@contents, @prevPos, @l)

    RETURN '...' + @retval + '...'
END
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

There are no errors here. Just open a new query window and press f5. After that if you face any errors pls let us know. Sometimes the Intellisense's local cache is not refreshed. For that press CTRL+SHIFT+R
Tried what you wrote and I still keep getting this error:

Msg 102, Level 15, State 1, Procedure HighLightSearch, Line 23
Incorrect syntax near '+'.

BTW, using SQL Server 2014 Management Studio, but also tried in SQL 2008 with exact same error.

Help!
Which SQL Server version you are using ?
2008
Avatar of Vitor Montalvão
You might have any strange character in that line.
Delete the line 23 and write it again.
NOTE: Do not use copy and paste. You really need to type everything).
Tried that...no difference.
However, this does work:

SELECT display_term FROM sys.dm_fts_parser(@searchTerm, 1033, 0, 1)

That tells me the nested portion is somehow throwing off Intellesense:

N'FORMSOF(FREETEXT, "' + @searchTerm + '")'
This also works:

 SELECT display_term FROM sys.dm_fts_parser(N'FORMSOF(FREETEXT, ' ')', 1033, 0, 1)

So, the inclusion of the  "' + @searchTerm + '" is the culprit
Problem is how do I insert the variable @searchTerm ???
OK, this results in no errors:

set @searchTerm=N'FORMSOF(FREETEXT, "'+@searchTerm+'")'
    
	DECLARE cur1 CURSOR FOR
    SELECT display_term FROM sys.dm_fts_parser(@searchTerm, 1033, 0, 1)

Open in new window

Maybe that is the way to do it??
ASKER CERTIFIED SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Russ Torlage
Russ Torlage

ASKER

Thanks for your help!