Paged data without temp table and without cursor

,

Stored procedure that return range of data from any table without using temp table or cursors.  You don't even need to use the IDENTITY field in your target table.  Check out and comments are welcomed

-- I used a table Customers from northwind database, table name can be used as variable
-- creating the store procedure
IF EXISTS (SELECT name 
	   FROM   sysobjects 
	   WHERE  name = N'aplus_sp_getDataRange' 
	   AND 	  type = 'P')
    DROP PROCEDURE aplus_sp_getDataRange
GO

CREATE PROCEDURE aplus_sp_getDataRange
		@pageSize int,
		@pageNumber int
AS
DECLARE @sql NVARCHAR(2000)
SET @sql = 	N' SELECT TOP ' + str(@pageSize) + ' E1.CustomerID,E1.ContactName FROM Customers E1 LEFT OUTER JOIN (SELECT  TOP ' 
		+ str(@pageSize*(@pageNumber-1))+ ' CustomerID FROM Customers ORDER BY CustomerID) AS E2 ' 
  		+ ' ON E1.CustomerID = E2.CustomerID WHERE E2.CustomerID IS NULL ORDER By E1.CustomerID'
EXEC ( @sql)
GO

--Deploying the stored procedure and let's try it
DECLARE @howManyRows int
SET 	@howManyRows = 10

EXEC aplus_sp_getDataRange @howManyRows,1  --page one
EXEC aplus_sp_getDataRange @howManyRows,2  --page two 
EXEC aplus_sp_getDataRange @howManyRows,3  --page three

Rate

Share

Share

Rate