Click here to Skip to main content
Click here to Skip to main content

Other Row Paging Technique Using T-SQL

, 27 Apr 2005
Rate this:
Please Sign up or sign in to vote.
Simple Row Paging Using T-SQL at Stored Procedure

Introduction

When we work with small data, our web page look so fine displaying Record. But when the data getting large, we will found Mr. Time Out Coming to say "Sorry Expired".

This method is different than other paging method. By using stored procedure we can get a table result that contain specific row rather than get the entire table.

Here the stored procedure routin :

CREATE PROCEDURE Get_MasterPart
(
Declare @ActivePage as BigInt = 1
Declare @PageCount as BigInt = 10
)
AS
Begin
Declare @StartDate as DateTime
SET @StartDate = GetDate()

--DECLARE @MidRow as BigInt
DECLARE @TotalRow as BigInt
DECLARE @TotalPage as BigInt
DECLARE @MidRow as BigInt
Select @TotalRow = Count(PartNumber) From Master_Part WITH (NOLOCK)
SET @MidRow = Round(@TotalRow/2,0)
SET @TotalPage = Round(@TotalRow / @PageCount,0)
IF @TotalRow > (@TotalPage * @PageCount) SET @TotalPage = @TotalPage + 1

DECLARE @RowPos as BigInt
DECLARE @ID as VarChar(100)
SET @RowPos = (@PageCount * @ActivePage) - @PageCount
IF @RowPos > @MidRow
Begin
SET @RowPos = @TotalRow - @RowPos 
SET ROWCOUNT @ROWPOS
SELECT @ID = PartNumber From Master_Part WITH (NOLOCK) ORDER BY PartNumber DESC
END
ELSE
Begin
SET @RowPos = @PageCount * @ActivePage
SET @RowPos = @RowPos - @PageCount + 1
SET ROWCOUNT @ROWPOS
SELECT @ID = PartNumber From Master_Part WITH (NOLOCK) ORDER BY PartNumber ASC
End

SET ROWCOUNT @PageCount
DECLARE @Part table(TotalPage Int DEFAULT 0, PartNumber VarChar(50), SparePart Char(255), PartOnHand int)
INSERT INTO @PART SELECT 0 as TotalPage, PartNumber,SparePart,PartOnHand FROM Master_Part WITH (NOLOCK) WHERE PartNumber >= @ID ORDER BY PartNumber ASC
UPDATE @Part SET TotalPage = @TotalPage
SELECT * FROM @Part Order By PartNumber
SELECT DateDiff(MilliSecond,@StartDate,GetDate())

END
GO

Now we can call the stored procedure using common execute Query function.

dim dt = dataset
dt =
DBServices.ExecuteQuery("Get_MasterPart" ,StoredProcedure,Sqlparameter)

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

About the Author

bluechip_asia
Web Developer
Indonesia Indonesia
No Biography provided

Comments and Discussions

 
Generalnice!! Pinsusssharaabi23-Aug-05 4:08 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140721.1 | Last Updated 28 Apr 2005
Article Copyright 2005 by bluechip_asia
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid