SQL Server Pagination Stored Procedure
With Free Text Search.

This code example uses SQL Server 2014 Standard and the AdventureWorks Database

Sometimes we may need to use pagination in SQL server stored procedure, this blog will show you how to do this using SQL server offset and fetch.

This example also uses FREETEXTTABLE for searching, but the code can easily be changed to use CONTAINSTABLE, see my other blog about how to do this. If you are looking for how to use pagination in an MVC website, take a look at this blog post.

This blog will also return the total records found using the OUT parameter.

The SQL code for the stored procedure is shown below.

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: 
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE spPagination
	-- Add the parameters for the stored procedure here
	@SearchTerm nvarchar(500),
	@Page INT,
	@NumberOfResults INT,
	@TotalCount INT OUT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @s nvarchar(500)
    SET @s='"' + @SearchTerm + '"'
	DECLARE @PageNumber INT
	SET @PageNumber = @Page
	DECLARE @RowsPerPage INT
	SET @RowsPerPage = @NumberOfResults

	--Count total records

	SELECT @TotalCount = (SELECT COUNT(ProductID) 
		FROM Production.Product AS FT_TBL   
			INNER JOIN FREETEXTTABLE(Production.Product,  Name, @s) AS KEY_TBL  
		ON FT_TBL.ProductID = KEY_TBL.[KEY])

    -- Insert statements for procedure here
	SELECT FT_TBL.Name, FT_TBL.ProductNumber  ,KEY_TBL.RANK  
		FROM Production.Product AS FT_TBL   
		INNER JOIN FREETEXTTABLE(Production.Product,  Name, @s) AS KEY_TBL  
	ON FT_TBL.ProductID = KEY_TBL.[KEY]  
	ORDER BY RANK DESC
		OFFSET ((@PageNumber -1) * @RowsPerPage) ROWS
			FETCH NEXT @RowsPerPage ROWS ONLY; 
		END
GO

To execute the code above in the query window, run the following:

DECLARE @count INT;
exec [dbo].[spPagination]  @SearchTerm = 'flat', @Page = 1, @NumberOfResults = 10, @TotalCount = @count OUTPUT
SELECT @count AS 'Number Of Products Found'

The above will return the following:

SQL Server Pagination using OFFSET FETCH with FREETEXTTABLE

One of the main things to note about the stored procedure is how I'm declaring the @SearchTerm, I declare a variable @s and then wrap the @SearchTerm  within double and single quotes, SET @s='"' + @SearchTerm + '"', this is not really required for FREETEXTTABLE, but it is required for CONTAINSTABLE.

Depending on how your tables are set up, this may not be the most efficient way to return the result set using offset fetch so you have how to play about with the code, but hopefully, it will give you an idea of how to use pagination in SQL Server, thanks for reading.