Custom Paging

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

--usp_GetEmpList 2,2,0
ALTER PROC [dbo].[usp_GetEmpList]

@startRowIndex int,

@maximumRows int,

@totalRows int OUTPUT

AS

DECLARE @first_id int, @startRow int

SET @startRowIndex = (@startRowIndex - 1) * @maximumRows

IF @startRowIndex = 0

SET @startRowIndex = 1

SET ROWCOUNT @startRowIndex

SELECT @first_id = CategoryId FROM Tbl_Category ORDER BY CategoryId
IF @startRowIndex = 1
set @first_id = @first_id
else
set @first_id = @first_id + 1

PRINT @first_id

SET ROWCOUNT @maximumRows

SELECT * FROM Tbl_Category WHERE CategoryId >= @first_id ORDER BY CategoryId

SET ROWCOUNT 0

-- GEt the total rows

SELECT @totalRows = COUNT(CategoryId) FROM Tbl_Category