CREATE procedure main_table_pwqzc(@pagesize int,@pageindex int,@docount bit,@this_id)asif(@docount=1)beginselect count(id) from luntan where this_id=@this_idendelsebegindeclare @indextable table(id int identity(1,1),nid int)declare @PageLowerBound intdeclare @PageUpperBound intset @PageLowerBound=(@pageindex-1)*@pagesizeset @PageUpperBound=@PageLowerBound+@pagesizeset rowcount @PageUpperBoundinsert into @indextable(nid) select id from luntan where this_id=@this_id order by reply_time descselect a.* from luntan a,@indextable t where a.id=t.nidand t.id>@PageLowerBound and t.id<=@PageUpperBound order by t.idendGO
存储过程会根据传入的参数@docount来确定是不是要返回所有要分页的记录总数特别是这两行set rowcount @PageUpperBoundinsert 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.nidand 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)DECLARE @strSortColumn varchar(200)DECLARE @operator char(2)DECLARE @type varchar(100)DECLARE @prec int
/*Set sorting variables.*/ IF CHARINDEX('DESC',@Sort)>0BEGIN SET @strSortColumn = REPLACE(@Sort, 'DESC', '') SET @operator = '<='ENDELSEBEGIN IF CHARINDEX('ASC', @Sort) = 0 SET @strSortColumn = REPLACE(@Sort, 'ASC', '') SET @operator = '>='END
IF CHARINDEX('.', @strSortColumn) > 0BEGIN SET @SortTable = SUBSTRING(@strSortColumn, 0, CHARINDEX('.',@strSortColumn)) SET @SortName = SUBSTRING(@strSortColumn, CHARINDEX('.',@strSortColumn) + 1, LEN(@strSortColumn))ENDELSEBEGIN SET @SortTable = @Tables SET @SortName = @strSortColumnEND
SELECT @type=t.name, @prec=c.precFROM sysobjects o JOIN syscolumns c on o.id=c.idJOIN systypes t on c.xusertype=t.xusertypeWHERE 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 < 1SET @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 + ' 'ENDELSEBEGIN SET @strSimpleFilter = '' SET @strFilter = ''ENDIF @Group IS NOT NULL AND @Group != ''SET @strGroup = ' GROUP BY ' + @Group + ' 'ELSESET @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
