Strange error in this SQL Server stored procedure
I found the following stored procedure from here, while creating this stored procedure in my database, I get this error message: (I'm running SQL Server 2008 Developer)
Msg 102, Level 15, State 1, Procedure HighLightSearch, Line 23 Incorrect syntax near '+'.
Here is the full Stored Procedure code:
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 occurances 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 > @maxL开发者_开发知识库en / 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
Try doing
DECLARE @var NVARCHAR(4000)
SET @var=N'FORMSOF(FREETEXT, "' + @searchTerm + '")'
DECLARE cur1 CURSOR FOR
SELECT display_term FROM sys.dm_fts_parser(@var, 1033, 0, 1)
精彩评论