Paging Stored Procedure

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 id 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 to return the total number of records to be paged based on the input parameter @ docount.
These two lines in particular
Set rowcount @ PageUpperBound
Insert into @ indextable (nid) select id from luntan where this_id = @ this_id order by reply_time desc

It's really amazing !! Set rowcount @ PageUpperBound when the number of records reaches @ PageUpperBound, the query will be stopped.
, Select id only extracts the id column into 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
This sentence only extracts the required records from the table, instead of all the records. It is combined to greatly improve the efficiency !!
Wonderful !!!!

Create procedure Paging_RowCount
(
@ Tables varchar (1000 ),
@ PK varchar (100 ),
@ Sort varchar (200) = 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 policypes 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