Click here to Skip to main content
15,867,686 members
Articles / Web Development / HTML
Article

Other Row Paging Technique Using T-SQL

Rate me:
Please Sign up or sign in to vote.
1.17/5 (6 votes)
27 Apr 2005 27.6K   24   2
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 :

SQL
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


Written By
Web Developer
Indonesia Indonesia
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
Generalnice!! Pin
sharaabi23-Aug-05 4:08
sharaabi23-Aug-05 4:08 
GeneralBeware using this Pin
totig28-Apr-05 0:28
totig28-Apr-05 0:28 

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

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