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'FORMSO F(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](@c ontents 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'FORMSO F(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
The issue is in this section: SELECT display_term FROM sys.dm_fts_parser(N'FORMSO
For some reason Intellesense does not like the @searchTerm variable.
This code is from a sample code in a blog:
CREATE FUNCTION [dbo].[HighLightSearch](@c
@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'FORMSO
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
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!
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
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).
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(@searchT erm, 1033, 0, 1)
That tells me the nested portion is somehow throwing off Intellesense:
N'FORMSOF(FREETEXT, "' + @searchTerm + '")'
However, this does work:
SELECT display_term FROM sys.dm_fts_parser(@searchT
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'FORMSO F(FREETEXT , ' ')', 1033, 0, 1)
So, the inclusion of the "' + @searchTerm + '" is the culprit
SELECT display_term FROM sys.dm_fts_parser(N'FORMSO
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)
Maybe that is the way to do it??
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for your help!