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