|
CREATE procedure main_table_pwqzc (@pagesize int @pageindex int @docount bit @this_id) as if(@docount=) begin select count(id) from luntan where this_id=@this_id end else begin declare @indextable table(id int identity()nid int) declare @PageLowerBound int declare @PageUpperBound int set @PageLowerBound=(@pageindex)*@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 aid=tnid and tid>@PageLowerBound and tid<=@PageUpperBound order by tid 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 aid=tnid and tid>@PageLowerBound and tid<=@PageUpperBound order by tid 而这句也只从表中取出所需要的记录而不是所有的记录结合起来极大的提高了效率!! 妙啊真的妙!!!! CREATE PROCEDURE Paging_RowCount ( @Tables varchar() @PK varchar() @Sort varchar() = NULL @PageNumber int = @PageSize int = @Fields varchar() = * @Filter varchar() = NULL @Group varchar() = NULL) AS /*Default Sorting*/ IF @Sort IS NULL OR @Sort = SET @Sort = @PK /*Find the @PK type*/ DECLARE @SortTable varchar() DECLARE @SortName varchar() DECLARE @strSortColumn varchar() DECLARE @operator char() DECLARE @type varchar() DECLARE @prec int /*Set sorting variables*/ IF CHARINDEX(DESC@Sort)> BEGIN SET @strSortColumn = REPLACE(@Sort DESC ) SET @operator = <= END ELSE BEGIN IF CHARINDEX(ASC @Sort) = SET @strSortColumn = REPLACE(@Sort ASC ) SET @operator = >= END IF CHARINDEX( @strSortColumn) > BEGIN SET @SortTable = SUBSTRING(@strSortColumn CHARINDEX(@strSortColumn)) SET @SortName = SUBSTRING(@strSortColumn CHARINDEX(@strSortColumn) + LEN(@strSortColumn)) END ELSE BEGIN SET @SortTable = @Tables SET @SortName = @strSortColumn END SELECT @type=tname @prec=cprec FROM sysobjects o JOIN syscolumns c on oid=cid JOIN systypes t on cxusertype=txusertype WHERE oname = @SortTable AND cname = @SortName IF CHARINDEX(char @type) > SET @type = @type + ( + CAST(@prec AS varchar) + ) DECLARE @strPageSize varchar() DECLARE @strStartRow varchar() DECLARE @strFilter varchar() DECLARE @strSimpleFilter varchar() DECLARE @strGroup varchar() /*Default Page Number*/ IF @PageNumber < SET @PageNumber = /*Set paging variables*/ SET @strPageSize = CAST(@PageSize AS varchar()) SET @strStartRow = CAST(((@PageNumber )*@PageSize + ) AS varchar()) /*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 |
