hi,

you can use the following procedure for pagination

-- =============================================
Create Proc [dbo].[zmGetPageDebiteurByDebNaam]
@DebNaam AS varchar(35),
@Startrecord AS Decimal,
@EndRecord AS Decimal,
@CdActief As varchar
AS
WITH Pagina AS(
SELECT dbo.tblDebiteur.DebNr, dbo.tblDebiteur.Naam, dbo.fncPlaats
(DebNr, CdLand, HuisnrNum, PostcdNum, PostcdAlf, PbHuisnrNum, PbPostcdNum,
PbPostcdAlf) AS NENPlaats, dbo.tblDebiteur.CdActief, ROW_NUMBER() 
OVER(ORDER BY dbo.tblDebiteur.Naam) AS  RowNr  FROM dbo.tblDebiteur
WHERE (dbo.tblDebiteur.Naam LIKE @DebNaam +  % ) 
AND CdActief Like @CdActief)
SELECT * FROM Pagina
WHERE RowNr BETWEEN @Startrecord AND @EndRecord;

I hope it will help you. 

    
    Hi,

here is a sample stored procedure code to do pagination, you can get the total 
number of pages by getting the total count of the results in an inner/outer query. 

how to show them up on UI, refer total row counts / limit per page gives page counts 
if results in first/last page doesnt match the limit you need to handle to avoid some
problems. 

------------------------------------------------------------------------------- 
-- Procedure name	: getTable 
------------------------------------------------------------------------------- 

CREATE PROCEDURE dbo.[getTable] 
@getID INT, 
@From INT, 
@To INT 
AS 
BEGIN 
BEGIN 
SELECT 
Results.getID, Results.Name 
FROM 
(SELECT 
getID = A.getID, 
Name, 
Row	= ROW_NUMBER() OVER (ORDER BY A.getID ASC) 
FROM 
dbo.mytable A 
WHERE 
(A.getID = @getID)) AS Results 
WHERE 
Results.Row BETWEEN @From AND @To 
END 
END