The following stored procedure is used to generate a simple full-text search result in SQL Server. The stored procedure returns search results, record counts and navigation result set.

CREATE PROCEDURE demoScoreReturnPage
@Phrase varchar(4000),
@Page int,
@RecordsPerPage int,
@PagesInNavigation int

AS

declare @ResultTable TABLE(rownum int identity,rank int, title varchar(500),description varchar(500),createdatetime dateTIme)
declare @NavTable TABLE(Page int)
declare @Counter as int
declare @TotalRecords as int
declare @PageTotal as int
declare @Start as int
declare @NavPage as int
declare @HighestRank as int

select @NavPage=@Page-1

--Insert search result into a table variable
insert into @ResultTable (rank,title,description,createdatetime) select t2.rank,title,description,createdatetime from webtitle as t1,CONTAINSTABLE(webtitle,*,@Phrase) as t2 where t1.dockey=t2.[key] order by rank desc

--Get the total number of rows
select @TotalRecords=count(*) from @ResultTable

--Calculate the total number of pages
select @PageTotal = @TotalRecords/@RecordsPerPage

--Calculate the start record index for the current page
select @Start=@NavPage*@RecordsPerPage

--Get the highest rank
select top 1 @HighestRank=rank from @ResultTable

--Select the correct sub result set
select () as score,title,description,createdatetime from @ResultTable where rownum>=@Start and rownum<@Start+@RecordsPerPage

--Select the total number of records and the total number of pages
select @TotalRecords as TotalRecordCount, @PageTotal as TotalPageCount

--Generate navigation result set, which will be hooked up to a repeater in an ASP.NET app.
declare @NavCount as int
declare @NavStartPage as int
declare @Value as int

select @NavStartPage = (@NavPage/@PagesInNavigation)*@PagesInNavigation+1
select @Counter=0

if(@NavStartPage>1 and @NavStartPage<)
begin
 select @Value=-(@NavStartPage-1)
 insert into @NavTable values (@Value)
end
while(@Counter<@PagesInNavigation and @NavStartPage+@Counter<)
begin
 select @Value=@NavStartPage+@Counter
 insert into @NavTable values (@Value)
 select @Counter=@Counter+1
end
if(@NavStartPage+@Counter<@PageTotal)
begin
 select @Value=-(@NavStartPage+@Counter)
 insert into @NavTable values (@Value)
end
select * from @NavTable