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

存储过程会根据传入的参数@docount来确定是不是要返回所有要分页的记录总数

特别是这两行

set rowcount @PageUpperBound

insert into @indextable(nid) select id from luntan where this_id=@this_id order by reply_time desc

真的是妙不可言!!set rowcount @PageUpperBound当记录数达到@PageUpperBound时就会停止处理查询

,select id 只把id列取出放到临时表里,select a.* from luntan a,@indextable t where a.id=t.nid

and t.id>@PageLowerBound and t.id<=@PageUpperBound order by t.id

而这句也只从表中取出所需要的记录,而不是所有的记录,结合起来,极大的提高了效率!!

妙啊,真的妙!!!!

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.phprm.com/database/253e5bafcc652b1ac715d710f41326bf.html

转载随意，但请附上文章地址:-)