In any application generating related links is little challenging and it is very hard to get accurate result. I have try to create example of related link query which will fetch close result of what you are looking for.
Consider an example of any website with article section and if you are trying to give related links using fulltext search query of sql server, than following query would be helpful.
If you are new to fulltext search and would like to understand more on how fulltext search works in sql server
Step 1: Check whether fulltext search is installed
Select SERVERPROPERTY('IsFullTextInstalled')
If this query returns a value of '1' then the component is installed.
Step 2: Create FullText Catalog
Create FullText Catalog MyDBNameCatalog
Step 3: Create FullText Index on Table
Create FullText Index on ArticlesTable
(ArticleTitle)
Key Index PK_ArticleTable_ArticleID on MyDBNameCatalog
with Change_Tracking Auto
Step 4: Creating Query - Example Query Related Links for Article in SQL Server
Select ftt.Rank, *
from ArticleTable
Inner Join FreeTextTable(ArticleTable, ArticleTitle, 'ISABOUT("My Search Text")') as ftt
on ftt.[Key] = ArticleTable.ArticleId
Order by ftt.Rank desc
Step 5: Now you are ready to create stored procedure which can return related links
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[ArticleTable_ReleatedLinks_FullTextSearch]
@ArticleId bigint,
@ArticleTitle varchar(500),
@iErrorCode int OUTPUT
AS
SET NOCOUNT ON
set @ArticleTitle = 'ISABOUT(' + @ArticleTitle + ')'
SELECT Top 20
ArticleTable.[ArticleId],
ArticleTable.[ArticleTitle]
FROM ArticleTable
Inner Join FreeTextTable(ArticleTable, ArticleTitle, @ArticleTitle) as ftt
on ftt.[Key] = ArticleTable.ArticleId
Where
ArticleTable.ArticleId != @ArticleId
ArticleTable.IsDeleted = 0
Order by ftt.Rank desc
-- Get the Error Code for the statement just executed.
SELECT @iErrorCode=@@ERROR
No comments:
Post a Comment