Technical Article

Paged data without temp table and without cursor 2

,

I modified mbenothmane's script..
plz refer to "Paged data without temp table and without cursor(http://www.sqlservercentral.com/scripts/contributions/947.asp)"

IF EXISTS (SELECT name 
   FROM   sysobjects 
   WHERE  name = N'mssqlorg_sp_getDataRange' 
   AND   type = 'P')
    DROP PROCEDURE mssqlorg_sp_getDataRange
GO

CREATE PROCEDURE mssqlorg_sp_getDataRange
@pageSize int,
@pageNumber int
AS
SET NOCOUNT ON
DECLARE @sql NVARCHAR(2000)

SET @sql =
'SELECT TOP ' + CONVERT(CHAR(10), @pageSize)  + ' E1.CustomerID,E1.ContactName 
FROM (SELECT TOP ' + CONVERT(CHAR(10), @pageSize*@pageNumber) + ' CustomerID, ContactName
FROM Customers ORDER BY CustomerID) AS E1
ORDER BY E1.CustomerID DESC'

EXEC sp_executesql @sql
GO

SET STATISTICS IO ON

DECLARE @howManyRows int
SET @howManyRows = 10

EXEC aplus_sp_getDataRange @howManyRows,1  --page one
EXEC mssqlorg_sp_getDataRange @howManyRows,1  --page one

EXEC aplus_sp_getDataRange @howManyRows,2  --page two 
EXEC mssqlorg_sp_getDataRange @howManyRows,2  --page two 

EXEC aplus_sp_getDataRange @howManyRows,3  --page three
EXEC mssqlorg_sp_getDataRange @howManyRows,3  --page two

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating