SQL Server Performance Forum – Threads Archive

recordset paging simulation

If I use stored procedures instead of dynamic SQL queries in ASP, I don’t know of any way of using recordset paging unless by simulating it in my stored procedure. This is what I normally use, and would like some tips on how I could make it better. CREATE PROCEDURE sp_Transactions_FillRange (
@AbsolutePage INT=1,
@PageSize INT=20,
@RecordCount int output
) AS SET NOCOUNT ON — create temporary table
CREATE TABLE #TempTable
(ID INT IDENTITY, TransID int, TransDate datetime, TransName varchar(50), TransAmt money, Complete bit) — fill temporary table
INSERT INTO #TempTable (TransID, TransDate, TransName, TransAmt, Complete)
(SELECT * FROM (SELECT TOP 100 PERCENT TransID, TransDate, TransName, TransAmt, Complete FROM Transactions) DERIVEDTBL) — set the paging sizes
DECLARE @FirstRec INT, @LastRec INT
SELECT @FirstRec = (@AbsolutePage – 1) * @PageSize + 1
SELECT @LastRec = (@AbsolutePage * @PageSize) — return requested records
SELECT * FROM #TempTable WHERE ID Between @FirstRec AND @LastRec
SELECT @RecordCount=(SELECT COUNT(*) FROM #TempTable) GO Thanks,
Rush

Since there is no order by, you can recieve different order each time you call the procedure. Even if you use order by modifications of base table (inserts, deletes, maybe even updates if column value you order by is changed) can change the order. You don’t have to select @recordCount = (select count(*) …) it is faster to select @RecordCount = @@rowCount after insert statement. … waiting for Satya to post a link.

Thanks Mmarovic and here is the related information:
http://www.15seconds.com/Issue/010308.htm
http://authors.aspalliance.com/brettb/EasyADORecordSetPaging.asp
http://www.aspfaqs.com/webtech/062899-1.shtml HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

It looks like I am doing it the right way, but it just seems like there should be a better way. Does it matter that I could be inserting 300,000 records into a temp table and only want 10 of them? It doesn’t seem reasonable.

If you need only partial number of rows from the source (other than SQL table) then its a good approach to load them to temp table and takeout required rows and then drop it. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Try this:
http://weblogs.sqlteam.com/jeffs/archive/2004/03/22/1085.aspx

good find! thanks!

]]>