High Performance Pagination


 

 

This is stored procedure with high performance

create PROCEDURE [dbo].[GetPageDataOut]

(

@tn nvarchar(30),–the name of the table

@idn nvarchar(20),–the name of primary key for the table

@pi int = 1,–current index of the page

@ps  int = 7,–size of the page

@rc int output,–total row (out parameter)

@pc int output—total page (out parameter)

)

AS

DECLARE @sql NVARCHAR(225),@sqlCount NVARCHAR(225)

SET @sqlCount = ‘SELECT @rc=COUNT([‘+@idn+’]),@pc=CEILING((COUNT(‘+@idn+’)+0.0)/’+ CAST(@ps AS VARCHAR)+’) FROM ‘ + @tn

EXEC SP_EXECUTESQL @sqlCount,N’@rc INT OUTPUT,@pc INT OUTPUT’,@rc OUTPUT,@pc OUTPUT

 

IF @pi = 1

BEGIN

SET @sql=’SELECT TOP ‘+str(@ps) +’ * FROM ‘+@tn

EXEC(@sql)

END

ELSE

BEGIN

SET NOCOUNT ON

DECLARE @PageLowerBound INT,@StartID INT

SET @PageLowerBound = @ps * (@pi-1)

IF @PageLowerBound<1

SET @PageLowerBound=1

SET ROWCOUNT @PageLowerBound

SET @sql=N’SELECT  @StartID = [‘+@idn+’] FROM ‘+@tn

print ‘@sql=’+@sql

EXEC sp_executesql @sql,N’@StartID int output’,@StartID output

SET ROWCOUNT 0

SET @sql=’SELECT TOP ‘+str(@ps) +’ * FROM ‘+@tn+’ WHERE [‘+@idn+’]>’+ str(@StartID)

EXEC(@sql)

SET NOCOUNT OFF

END

 

–test query

declare @rc int,@pc int

exec GetPageDataOut ‘Ams_Area’,’ar_id’,2,15,@rc output,@pc output

select @rc,@pc

Social Media


Categories



All Posts



Contact Info