Technical Article

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

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating