This Transact-SQL Store Procedure allows you to do paging on your SQL table by providing only two parameters:
- Current page
- Record to display per page
It returns chosen records and also total count as output parameter if you need to display this information or calculate how many pages you have in paging control.
CREATE PROCEDURE [dbo].[YourProcedureName]
@CurrentPage int,
@RecordsPerPage int,
@Count int OUTPUT
AS
BEGIN
-- The number of rows affected by the different commands
-- does not interest the application, so turn NOCOUNT ON
SET NOCOUNT ON
-- Determine the first record and last record
DECLARE @FirstRecord int, @LastRecord int
SELECT @FirstRecord = (@CurrentPage - 1) * @RecordsPerPage
SELECT @LastRecord = (@CurrentPage * @RecordsPerPage + 1)
SET @Count = (SELECT COUNT(*) FROM YourTable);
WITH TempResult as
(
SELECT ROW_NUMBER() OVER(ORDER BY Field1 DESC) as RowNumber,
Field1,
Field2
FROM YourTable
)
SELECT TOP (@LastRecord - 1) *
FROM TempResult
WHERE RowNumber > @FirstRecord AND
RowNumber < @LastRecord
-- Turn NOCOUNT back OFF
SET NOCOUNT OFF
END
