存储过程|分页

CREATE procedure main_table_pwqzc
(pagesize int,
pageindex int,
docount bit,
this_id)
as
if(docount=1)
begin
select count(id) from luntan where
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 order by reply_time desc
select a.* from luntan a,indextable t where a.id=t.nid
and t.id>PageLowerBound and t.id< order by t.id
end
GO

存储过程会根据传入的参数docount来确定是不是要返回所有要分页的记录总数
特别是这两行
set rowcount PageUpperBound
insert into indextable(nid) select id from luntan where 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< 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)
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