Several paging stored procedures

Source: Internet
Author: User
Tags rowcount

Process one:

Select Top N records * from article table where ID not in (select top m record ID from article table ORDER BY id DESC) ORDER BY id DESC


Process two:

Select Top N records * from article table where ID < (select min (id) from (select top m record ID from article table ORDER by id desc) as tbltmp) or Der by id Desc


--Simple and universal

CREATE PROCEDURE [dbo]. [PageView]

@select VARCHAR (max),

@CurrentPage INT,

@PageSize INT

As

BEGIN

DECLARE @sql NVARCHAR (max)

DECLARE @RecordCurrent INT

DECLARE @PageCount INT

DECLARE @RecordCount INT

SET NOCOUNT on

Set @sql = ' Select @RecordCount =count (*) from (' [email protected]+ ') a '

EXEC sp_executesql @sql, N ' @RecordCount int output ', @RecordCount output

SET @PageCount = (@[email protected])/@PageSize

IF ISNULL (@CurrentPage, 0) <1

SET @CurrentPage =1

ELSE if ISNULL (@CurrentPage, 0) > @PageCount

SET @[email protected]

SELECT @CurrentPage as CurrentPage, @RecordCount as RecordCount, @PageSize as PageSize, @PageCount as PageCount

Set @sql = ' select * FROM (' [email protected]+ ') A where rownumber between ' +cast ((@CurrentPage-1) * @PageSize +1 as varchar) + ' and ' +cast (@CurrentPage * @PageSize as varchar)

EXEC (@sql)

END

--A stored procedure that uses the not in method

Create PROCEDURE Getpagedatabynotin

@PageIndex int,/**//* Current Page */

@PageSize int/**//*/Page Size */

As

DECLARE @starttime datetime

Set @starttime =getdate ()

IF @PageIndex > 0

BEGIN

SET NOCOUNT on;

DECLARE @PageLowerBound int

DECLARE @StartID int

DECLARE @sql varchar (225)

SET @PageLowerBound = @PageSize * (@PageIndex-1)

IF @PageLowerBound <1

SET @PageLowerBound =1

Print @PageLowerBound

Select Top (@PageSize) * from table where [ar_id] not in (select Top ((@PageSize) * (@PageIndex-1)) ar_id from table)

EXEC (@sql)

SET NOCOUNT off;

END

print ' time consuming = ' +convert (varchar (), DATEDIFF (MS, @starttime, GETDATE ()))

-------------------------------------------------------------------------------------------------------

--Paging stored procedure using ROWCOUNT

Create PROCEDURE Getpagedata

@PageIndex int,/**//* Current Page */

@PageSize int/**//*/Page Size */

As

DECLARE @starttime datetime

Set @starttime =getdate ()

IF @PageIndex > 0

BEGIN

SET NOCOUNT on;

DECLARE @PageLowerBound int

DECLARE @StartID int

DECLARE @sql varchar (225)

SET @BeginIndex = @PageSize * (@PageIndex-1)

IF @BeginIndex <1

SET @BeginIndex =1

SET ROWCOUNT @BeginIndex

SELECT @StartID = [ar_id] FROM table ORDER by ar_id

Print @StartID

SET ROWCOUNT 0

SET @sql = ' select Top ' +str (@PageSize) + ' * from table where [ar_id]>= ' + str (@StartID) + ' ORDER by [ar_id] '

EXEC (@sql)

SET NOCOUNT off;

END

print ' time consuming = ' +convert (varchar (), DATEDIFF (MS, @starttime, GETDATE ()))


-----------------------------------------------------------------------------------------------------

Test query a table with 100W data, showing 10 data per page

Stored Procedure 1th page 10th page 100th page 1000th page No. 5000 page

Getpagedatabynotin 0 0 126 13530 wait more than 2 minutes impatient ....

Getpagedata 0 0 0 16 76


------------------------------------------------------------------------------------------------------------

Last stored procedure used (recommended):

Create PROCEDURE Getpagedata

(

@TableName varchar (,--) Table name

@IDName varchar,--Table primary Key Name

@PageIndex int,--Current page

@PageSize int--per page size

)

As

IF @PageIndex > 0

BEGIN

SET NOCOUNT ON

DECLARE @PageLowerBound int, @StartID int, @sql nvarchar (225)

SET @PageLowerBound = @PageSize * (@PageIndex-1)

IF @PageLowerBound <1

SET @PageLowerBound =1

SET ROWCOUNT @PageLowerBound

SET @sql =n ' SELECT @StartID = [' [email protected]+ '] from ' [E-mail protected]+ ' ORDER by ' [email protected]

EXEC sp_executesql @sql, N ' @StartID int output ', @StartID output

SET ROWCOUNT 0

SET @sql = ' select Top ' +str (@PageSize) + ' * from ' [email protected]+ ' WHERE [' [email protected]+ ']>= ' + str (@StartID) + ' ORDER by [' [email protected]+ '] '

EXEC (@sql)

SET NOCOUNT OFF

END


Several paging stored procedures

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.