Wonderful and almost perfect paging stored procedure_PHP tutorial

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 will determine whether to return the total number of all records to be paged according to the passed parameter @docount
Especially These two lines
set rowcount @PageUpperBound
insert into @indextable(nid) select id from luntan where this_id=@this_id order by reply_time desc
are really wonderful!! set rowcount @PageUpperBound when recording When the number reaches @PageUpperBound, it will stop processing the query
, select id only takes out the id column and puts it 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
And this sentence only takes out the required records from the table, not all records. Combined, it greatly improves Efficiency!!
Wonderful, really 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)

http://www.bkjia.com/PHPjc/631236.htmlwww.bkjia.comtruehttp: //www.bkjia.com/PHPjc/631236.htmlTechArticleCREATE 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...