RSS
 

Posts Tagged ‘paging’

Stored Procedure for paging in SQL Server 2011

27 Jan

Paging is much easier and faster in SQL Server 2011. Here is the Stored Procedure for paging which gets two parameters as input; @PageNumber and @RowsPerPage

CREATE PROCEDURE SQL_2011_PAGING
(
@PageNumber INT,
@RowsPerPage INT
)
AS
 
SELECT
No, Name, Surname, Class
FROM dbo.Students
ORDER BY No
OFFSET (@PageNumber-1) * @RowsPerPage ROWS
FETCH NEXT @RowsPerPage ROWS ONLY
GO

If you use SQL Server 2008 or earlier versions, you can read the article I’ve written before; Simple stored procedure for paging

Special thanks to Turgay

 
2 Comments

Posted in English, SQL Server

 

Simple stored procedure for paging

12 Jun

This SP is useful where paging is required on huge tables. It selects the exact data instead of getting whole table data.

It works on one table, so that if you have to select from multiple tables, then you can create a view and pass it’s name to this SP as @table parameter. Get the records of @page, from @table, which are order by @orderby columns, @perpage number of records for page.

Following example gets the 23. page from “Customers” table and 50 lines per page. Also records are ordered by “Name” and “Surname” column.

@TABLE = 'Customers'
@orderby = 'Name, Surname'
@perpage = 50
@page = 23
 
-- =============================================
-- Author: mSu
-- Create date: 20100106
-- Description: get @perpage records of the page @page from the table @table order by @orderby
-- exec get_page 'customer','ID',20,17
-- =============================================
ALTER PROC [dbo].[get_page]
@TABLE AS VARCHAR(64),
@orderby AS VARCHAR(128),
@perpage AS INT,
@page AS INT
AS
DECLARE @START AS INT
DECLARE @END AS INT
SET @START = (@page-1) * @perpage + 1
SET @END = @perpage * @page
 
EXEC ('SELECT TOP (' + @perpage + ') * FROM
(SELECT ROW_NUMBER() OVER (ORDER BY ' + @orderby + ') AS ROWNUM,* FROM ' + @TABLE + ' (NOLOCK)) TMP
WHERE ROWNUM BETWEEN ' + @START + ' AND ' + @END + ' ORDER BY ROWNUM')
 
No Comments

Posted in English, SQL Server