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

CREATE PROCEDURE demoScoreComplexReturnPage
@Phrase varchar(4000), --Search phrase.
@Year int,  --Search year.
@C0 int,   --Category All.
@C1 int,   --Category 1.
@C2 int,   --Category 2 .
@C3 int,   --Category 3.
@C4 int,   --Category 4.
@C5 int,   --Category 5.
@C6 int,   --Category 6.
@Page int,
@RecordsPerPage int,
@PagesInNavigation int

AS

declare @ResultTable TABLE(rownum int identity,rank int, title varchar(500), description varchar(500), createdatetime DateTime)
declare @categories TABLE(categoryid int)
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. A lot of processing is done here to avoid having to use temp tables with dynamic SQL, which is slower than table variables.
if(@C0=1)
begin
    if(@year=0)
    begin
        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
    end
    else
    begin
        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] and order by rank desc
    end
end
else begin
    if (@C1=1)  insert into @Categories values (1)
    if (@C2=1)  insert into @Categories values (2)
    if (@C3=1)  insert into @Categories values (3)
    if (@C4=1)  insert into @Categories values (4)
    if (@C5=1)  insert into @Categories values (5)
    if (@C6=1)  insert into @Categories values (6)

    if (@year=0)
    begin
        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] and categoryid in (select categoryid from @categories) order by rank desc
    end
    else begin
        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] and and categoryid in (select categoryid from @categories) order by rank desc
    end
end

--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