When you fetch the data from database, you may get hundreds or even thousands of rows. Showing all of those rows in a single page is not a good idea. You should definitely split that records into multipal pages. That is called pagination. Normally, developers use the sever side language such as VB.NET, C#, PHP to generate it. However, different developer use different styles of pagination with different languages. T-SQL can also be used to draw paging control. This is the SQL Server User-Defined Function to generate it.

Code Snippet

CREATE FUNCTION [dbo].[ufn_GeneratePagination] 
(	
	@RecordsPerPage NUMERIC(12,0), 
	@TotalRecords NUMERIC(12,0),
	@SelectedPage INT,
	@Link VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
AS
BEGIN

	DECLARE @HTML AS VARCHAR(MAX)
	DECLARE @NumberOfPages INT
	DECLARE @MinPageNumber INT
	DECLARE @MaxPageNumber INT
		
	SELECT @NumberOfPages = 0, @MinPageNumber = 0, @MaxPageNumber = 0
	
	SET @NumberOfPages = CEILING(@TotalRecords / @RecordsPerPage)
	
	SET @MinPageNumber = (@SelectedPage - 5)
	SET @MaxPageNumber = (@SelectedPage + 5)
 
	IF(@MinPageNumber < 1)
	BEGIN
		 SET @MinPageNumber = 1
		 SET @MaxPageNumber = 11
		 
		 IF(@MaxPageNumber > @NumberOfPages)
			SET @MaxPageNumber = @NumberOfPages 
		 ELSE
			SET @MaxPageNumber = 11
	END
	 
	IF(@MaxPageNumber > @NumberOfPages)
	BEGIN
		 SET @MaxPageNumber = @NumberOfPages
		 SET @MinPageNumber = @MaxPageNumber - 10
		 
		 IF(@MinPageNumber < 1)
			SET @MinPageNumber =  1
		 ELSE
			SET @MinPageNumber = @MaxPageNumber - 10
	END
	 
	IF(@TotalRecords <= @RecordsPerPage)
	BEGIN
		SET @MinPageNumber = 1
		SET @MaxPageNumber = 1
	END
	
	DECLARE @Goto AS VARCHAR(MAX)
	DECLARE @Pages AS VARCHAR(MAX)
	DECLARE @First VARCHAR(MAX)
	DECLARE @Prev VARCHAR(MAX)
	DECLARE @Last VARCHAR(MAX)
	DECLARE @Next VARCHAR(MAX)
	DECLARE @PageInfo VARCHAR(MAX)
	DECLARE @RecordInfo VARCHAR(MAX)
	
	IF(ISNULL(@SelectedPage,0)=0) SET @SelectedPage = 1

	DECLARE @StartRow INT
	DECLARE @EndRow INT

	SET @StartRow = (@RecordsPerPage * @SelectedPage) - @RecordsPerPage + 1;
	SET @EndRow = (@StartRow + @RecordsPerPage-1);
	
	IF(@EndRow > @TotalRecords)
	BEGIN
		SET @EndRow = @TotalRecords
	END
	
	SELECT @Goto='',@Pages='',@First='',@Last='',@Prev='',@Next='',@PageInfo = '',@RecordInfo=''
	
	IF(@SelectedPage > 1 AND @NumberOfPages > 1) 
	BEGIN		
		SET @First = '<span><a href="' + REPLACE(@Link,'{0}','1') + '">First</a></span>'
		SET @Prev = '<span><a href="' + REPLACE(@Link,'{0}',CAST(@SelectedPage - 1 AS VARCHAR(50))) + '">Prev</a></span>'
	END
	 
	 IF(@SelectedPage < @NumberOfPages AND @NumberOfPages > 1) 
	 BEGIN
		SET @Last = '<span><a href="' + REPLACE(@Link,'{0}',CAST(@NumberOfPages AS VARCHAR(50))) + '">Last</a></span>'
		SET @Next = '<span><a href="' + REPLACE(@Link,'{0}',CAST(@SelectedPage + 1 AS VARCHAR(50))) + '">Next</a></span>'
	 END
	 
	 DECLARE @Counter INT 	 
	 SET @Counter = @MaxPageNumber
	 
	 WHILE(@Counter >= @MinPageNumber)
	 BEGIN
	 		
		SET @Pages = @Pages + '<span' + CASE WHEN @[email protected] THEN ' style="background-color:#ddd;" ' ELSE '' END + '><a href="' + REPLACE(@Link,'{0}',CAST(@Counter AS VARCHAR(50))) + '">' + CAST(@Counter AS VARCHAR(50)) + '</a></span>'
		SET @Counter = @Counter - 1
		
	 END
	
	--SET @Goto = '<span><a href="#">Go</a></span>'
	
	SET @PageInfo = '<span>Page ' + CAST(@SelectedPage AS VARCHAR(12)) + ' of ' + CAST(@MaxPageNumber AS VARCHAR(12)) +  + '</span>'
	
	IF(@StartRow = @EndRow)
	BEGIN
		SET @RecordInfo = '<span style="float:left;margin-left:0px;">Record ' + CAST(@StartRow AS VARCHAR(12)) + ' of ' + CAST(@TotalRecords AS VARCHAR(12))+ '</span>'
	END
	ELSE
	BEGIN
		SET @RecordInfo = '<span style="float:left;margin-left:0px;">Record ' + CAST(@StartRow AS VARCHAR(12)) + ' to ' + CAST(@EndRow AS VARCHAR(12)) + ' of ' + CAST(@TotalRecords AS VARCHAR(12))+ '</span>'
	END
	
	SET @HTML = '<div class="paging">' + @Last + @Next + @Pages + @Prev + @First + @PageInfo + @RecordInfo + '</div>'
		
	RETURN @HTML	

END

Sample Usage

SELECT dbo.[ufn_GeneratePagination](10,500,5,'http://www.domain.com/page/{0}') 

--See sample output in below image.

Note

Pagination

Paramerters For Sql Pagination Function

@RecordsPerPageHow many records you want to show in a page.
@TotalRecordsTotal number of records.
@SelectedPageCurrent Page Index.
@LinkFormat of the url string. {0} will be replaced with page number.

Supporting CSS

.paging 
{
	border: none;
	width: 100%;
	height: 22px;
	border: solid 0px white;
	font-size: 8pt;
	margin-top: 10px;
	margin-bottom: 10px;
}

.paging span 
{
	border: solid 1px #CCC;
	margin-left: 1px;
	padding: 2px 4px 2px 4px;
	height: 15px;
	overflow: visible;
	float: right;
}




author's photo

Author : Blue Cloud

Just a normal software developer who love to write code. Developing professional software since 1999. After working in the software industry for many years, I've started my own website to share knowledges and experiences.

COMMENTS
blog comments powered by Disqus