Wonderful near-perfect paging stored procedure

Stored Procedures | paging

CREATE procedure Main_table_pwqzc
(@pagesize int,
@pageindex int,
@docount bit,
@this_id)
As
if (@docount =1)
Begin
Select COUNT (id) from Luntan where this_id= @this_id
End
Else
Begin
Declare @indextable table (ID int identity (1,1), nid int)
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
Set @PageLowerBound = (@pageindex-1) * @pagesize
Set @PageUpperBound = @PageLowerBound + @pagesize
SET ROWCOUNT @PageUpperBound
Insert into @indextable (NID) Select IDs from Luntan where this_id= @this_id ORDER BY reply_time Desc
Select A.* from Luntan A, @indextable t where A.id=t.nid
and t.id> @PageLowerBound and t.id<= @PageUpperBound ORDER by t.id
End
Go

The stored procedure determines whether the total number of records to be paged is returned based on the parameters passed in @docount
Especially in these two lines.
SET ROWCOUNT @PageUpperBound
Insert into @indextable (NID) Select IDs from Luntan where this_id= @this_id ORDER BY reply_time Desc

It's really fantastic!! SET ROWCOUNT @PageUpperBound stop processing queries when the number of records reaches @pageupperbound
, the Select ID only takes the ID column out of the temporary table, select A.* from Luntan A, @indextable t where A.id=t.nid
and t.id> @PageLowerBound and t.id<= @PageUpperBound ORDER by t.id
And this sentence also only from the table to take out the required records, rather than all the records, combined, greatly improve the efficiency!!
Wonderful, really wonderful!!!!

CREATE PROCEDURE Paging_rowcount
(
@Tables varchar (1000),
@PK varchar (100),
@Sort varchar = NULL,
@PageNumber int = 1,
@PageSize int = 10,
@Fields varchar (1000) = ' * ',
@Filter varchar (1000) = NULL,
@Group varchar (1000) = NULL)
As

/*default sorting*/
IF @Sort is NULL OR @Sort = '
SET @Sort = @PK

/*find the @PK type*/
DECLARE @SortTable varchar (100)
DECLARE @SortName varchar (100)
DECLARE @strSortColumn varchar (200)
DECLARE @operator char (2)
DECLARE @type varchar (100)
DECLARE @prec int

/*set sorting variables.*/
IF CHARINDEX (' DESC ', @Sort) >0
BEGIN
SET @strSortColumn = REPLACE (@Sort, ' DESC ', ')
SET @operator = ' <= '
End
ELSE
BEGIN
IF CHARINDEX (' ASC ', @Sort) = 0
SET @strSortColumn = REPLACE (@Sort, ' ASC ', ')
SET @operator = ' >= '
End

IF CHARINDEX ('. ', @strSortColumn) > 0
BEGIN
SET @SortTable = SUBSTRING (@strSortColumn, 0, CHARINDEX ('. ', @strSortColumn))
SET @SortName = SUBSTRING (@strSortColumn, CHARINDEX ('. ', @strSortColumn) + 1, LEN (@strSortColumn))
End
ELSE
BEGIN
SET @SortTable = @Tables
SET @SortName = @strSortColumn
End

SELECT @type =t.name, @prec =c.prec
From sysobjects o
JOIN syscolumns C on o.id=c.id
JOIN systypes T on C.xusertype=t.xusertype
WHERE o.name = @SortTable and c.name = @SortName

IF CHARINDEX (' char ', @type) > 0
SET @type = @type + ' (' + CAST (@prec as varchar) + ') '

DECLARE @strPageSize varchar (50)
DECLARE @strStartRow varchar (50)
DECLARE @strFilter varchar (1000)
DECLARE @strSimpleFilter varchar (1000)
DECLARE @strGroup varchar (1000)

/*default Page number*/
IF @PageNumber < 1
SET @PageNumber = 1

/*set Paging variables.*/
SET @strPageSize = CAST (@PageSize as varchar (50))
SET @strStartRow = CAST ((@PageNumber-1) * @PageSize + 1) as varchar (50))

/*set Filter & Group variables.*/
IF @Filter is not NULL and @Filter!= '
BEGIN
SET @strFilter = ' WHERE ' + @Filter + '
SET @strSimpleFilter = ' and ' + @Filter + '
End
ELSE
BEGIN
SET @strSimpleFilter = '
SET @strFilter = '
End
IF @Group is not NULL and @Group!= '
SET @strGroup = ' GROUP by ' + @Group + '
ELSE
SET @strGroup = '

/*execute Dynamic query*/
EXEC (
'
DECLARE @SortColumn ' + @type + '
SET RowCount ' + @strStartRow + '
SELECT @SortColumn = ' + @strSortColumn + ' from ' + @Tables + @strFilter + ' + @strGroup + ' ORDER BY ' + @Sort + '
SET RowCount ' + @strPageSize + '
SELECT ' + @Fields + ' from ' + @Tables + ' WHERE ' + @strSortColumn + @operator + ' @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
'
)
Go