自己收集比较强大的分页存储过程 推荐

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[proc_page]
(
@tblName nvarchar(200), ----要显示的表或多个表的连接lihu
@fldName nvarchar(500) = *, ----要显示的字段列表
@pageSize int = 10, ----每页显示的记录个数
@page int = 1, ----要显示那一页的记录
@fldSort nvarchar(200) = null, ----排序字段列表或条件
@Sort bit = 0, ----排序方法，0为升序，1为降序(如果是多字段排列Sort指代最后一个排序字段的排列顺序(最后一个排序字段不加排序标记)--程序传参如： SortA Asc,SortB Desc,SortC )
@strCondition nvarchar(1000), ----查询条件,不需where
@ID nvarchar(150), ----主表的主键
@Dist bit = 0, ----是否添加查询字段的 DISTINCT 默认0不添加/1添加
@pageCount int = 1 output, ----查询结果分页后的总页数
@Counts int = 1 output ----查询到的记录数
)
AS
SET NOCOUNT ON
Declare @sqlTmp nvarchar(1000) ----存放动态生成的SQL语句
Declare @strTmp nvarchar(1000) ----存放取得查询结果总数的查询语句
Declare @strID nvarchar(1000) ----存放取得查询开头或结尾ID的查询语句
Declare @strSortType nvarchar(10) ----数据排序规则A
Declare @strFSortType nvarchar(10) ----数据排序规则B
Declare @SqlSelect nvarchar(50) ----对含有DISTINCT的查询进行SQL构造
Declare @SqlCounts nvarchar(50) ----对含有DISTINCT的总数查询进行SQL构造
declare @timediff datetime --耗时测试时间差
select @timediff=getdate()
if @Dist = 0
begin
set @SqlSelect = select
set @SqlCounts = Count(*)
end
else
begin
set @SqlSelect = select distinct
set @SqlCounts = Count(DISTINCT +@ID+)
end
if @Sort=0
begin
set @strFSortType= ASC
set @strSortType= DESC
end
else
begin
set @strFSortType= DESC
set @strSortType= ASC
end
--------生成查询语句--------
--此处@strTmp为取得查询结果数量的语句
if @strCondition is null or @strCondition= --没有设置显示条件
begin
set @sqlTmp = @fldName + From + @tblName
set @strTmp = @SqlSelect+ @Counts=+@SqlCounts+ FROM +@tblName
set @strID = From + @tblName
end
else
begin
set @sqlTmp = + @fldName + From + @tblName + where (1>0) + @strCondition
set @strTmp = @SqlSelect+ @Counts=+@SqlCounts+ FROM +@tblName + where (1>0) + @strCondition
set @strID = From + @tblName + where (1>0) + @strCondition
end
----取得查询结果总数量-----
exec sp_executesql @strTmp,N@Counts int out ,@Counts out
declare @tmpCounts int
if @Counts = 0
set @tmpCounts = 1
else
set @tmpCounts = @Counts
--取得分页总数
set @pageCount=(@tmpCounts+@pageSize-1)/@pageSize
/**当前页大于总页数 取最后一页**/
if 2>@pageCount
set 2=@pageCount
--/*-----数据分页2分处理-------*/
declare @pageIndex int --总数/页大小
declare @lastcount int --总数%页大小
set @pageIndex = @tmpCounts/@pageSize
set @lastcount = @tmpCounts%@pageSize
if @lastcount > 0
set @pageIndex = @pageIndex + 1
else
set @lastcount = @pagesize
--//***显示分页
if @strCondition is null or @strCondition= --没有设置显示条件
begin
if @pageIndex<2 or 2<=@pageIndex / 2 + @pageIndex % 2 --前半部分数据处理
begin
if 2=1
set @strTmp=@SqlSelect+ top + CAST(@pageSize as VARCHAR(4))+ + @fldName+ from +@tblName
+ order by + @fldSort + + @strFSortType
else
begin
if @Sort=1
begin
set @strTmp=@SqlSelect+ top + CAST(@pageSize as VARCHAR(4))+ + @fldName+ from +@tblName
+ where +@ID+ <(select min(+ @ID +) from (+ @SqlSelect+ top + CAST(@pageSize*(2-1) as Varchar(20)) + + @ID + from +@tblName
+ order by + @fldSort + + @strFSortType+) AS TBMinID)
+ order by + @fldSort + + @strFSortType
end
else
begin
set @strTmp=@SqlSelect+ top + CAST(@pageSize as VARCHAR(4))+ + @fldName+ from +@tblName
+ where +@ID+ >(select max(+ @ID +) from (+ @SqlSelect+ top + CAST(@pageSize*(2-1) as Varchar(20)) + + @ID + from +@tblName
+ order by + @fldSort + + @strFSortType+) AS TBMinID)
+ order by + @fldSort + + @strFSortType
end
end
end
else
begin
set 2= @pageIndex-2+1 --后半部分数据处理
if 2<= 1 --最后一页数据显示
set @strTmp=@SqlSelect+ * from (+@SqlSelect+ top + CAST(@lastcount as VARCHAR(4))+ + @fldName+ from +@tblName
+ order by + @fldSort + + @strSortType+) AS TempTB+ order by + @fldSort + + @strFSortType
else
if @Sort=1
begin
set @strTmp=@SqlSelect+ * from (+@SqlSelect+ top + CAST(@pageSize as VARCHAR(4))+ + @fldName+ from +@tblName
+ where +@ID+ >(select max(+ @ID +) from(+ @SqlSelect+ top + CAST(@pageSize*(2-2)+@lastcount as Varchar(20)) + + @ID + from +@tblName
+ order by + @fldSort + + @strSortType+) AS TBMaxID)
+ order by + @fldSort + + @strSortType+) AS TempTB+ order by + @fldSort + + @strFSortType
end
else
begin
set @strTmp=@SqlSelect+ * from (+@SqlSelect+ top + CAST(@pageSize as VARCHAR(4))+ + @fldName+ from +@tblName
+ where +@ID+ <(select min(+ @ID +) from(+ @SqlSelect+ top + CAST(@pageSize*(2-2)+@lastcount as Varchar(20)) + + @ID + from +@tblName
+ order by + @fldSort + + @strSortType+) AS TBMaxID)
+ order by + @fldSort + + @strSortType+) AS TempTB+ order by + @fldSort + + @strFSortType
end
end
end
else --有查询条件
begin
if @pageIndex<2 or @page<=@pageIndex / 2 + @pageIndex % 2 --前半部分数据处理
begin
if @page=1
set @strTmp=@SqlSelect+ top + CAST(@pageSize as VARCHAR(4))+ + @fldName+ from +@tblName
+ where 1=1 + @strCondition + order by + @fldSort + + @strFSortType
else if(@Sort=1)
begin
set @strTmp=@SqlSelect+ top + CAST(@pageSize as VARCHAR(4))+ + @fldName+ from +@tblName
+ where +@ID+ <(select min(+ @ID +) from (+ @SqlSelect+ top + CAST(@pageSize*(@page-1) as Varchar(20)) + + @ID + from +@tblName
+ where (1=1) + @strCondition + order by + @fldSort + + @strFSortType+) AS TBMinID)
+ + @strCondition + order by + @fldSort + + @strFSortType
end
else
begin
set @strTmp=@SqlSelect+ top + CAST(@pageSize as VARCHAR(4))+ + @fldName+ from +@tblName
+ where +@ID+ >(select max(+ @ID +) from (+ @SqlSelect+ top + CAST(@pageSize*(@page-1) as Varchar(20)) + + @ID + from +@tblName
+ where (1=1) + @strCondition + order by + @fldSort + + @strFSortType+) AS TBMinID)
+ + @strCondition + order by + @fldSort + + @strFSortType
end
end
else
begin
set 2= @pageIndex-2+1 --后半部分数据处理
if 2<= 1 --最后一页数据显示
set @strTmp=@SqlSelect+ * from (+@SqlSelect+ top + CAST(@lastcount as VARCHAR(4))+ + @fldName+ from +@tblName
+ where (1=1) + @strCondition + order by + @fldSort + + @strSortType+) AS TempTB+ order by + @fldSort + + @strFSortType
else if(@Sort=1)
set @strTmp=@SqlSelect+ * from (+@SqlSelect+ top + CAST(@pageSize as VARCHAR(4))+ + @fldName+ from +@tblName
+ where +@ID+ >(select max(+ @ID +) from(+ @SqlSelect+ top + CAST(@pageSize*(@page-2)+@lastcount as Varchar(20)) + + @ID + from +@tblName
+ where (1=1) + @strCondition + order by + @fldSort + + @strSortType+) AS TBMaxID)
+ + @strCondition+ order by + @fldSort + + @strSortType+) AS TempTB+ order by + @fldSort + + @strFSortType
else
set @strTmp=@SqlSelect+ * from (+@SqlSelect+ top + CAST(@pageSize as VARCHAR(4))+ + @fldName+ from +@tblName
+ where +@ID+ <(select min(+ @ID +) from(+ @SqlSelect+ top + CAST(@pageSize*(@page-2)+@lastcount as Varchar(20)) + + @ID + from +@tblName
+ where (1=1) + @strCondition + order by + @fldSort + + @strSortType+) AS TBMaxID)
+ + @strCondition+ order by + @fldSort + + @strSortType+) AS TempTB+ order by + @fldSort + + @strFSortType
end
end
------返回查询结果-----
exec sp_executesql @strTmp
select datediff(ms,@timediff,getdate()) as 耗时
print @strTmp
SET NOCOUNT OFF

Create PROC P_viewPage
/**//*
nzperfect [no_mIss] 高效通用分页存储过程(双向检索) 2007.5.7 QQ:34813284
敬告：适用于单一主键或存在唯一值列的表或视图
ps:Sql语句为8000字节,调用时请注意传入参数及sql总长度不要超过指定范围
*/
@TableName VARCHAR(200), --表名
@FieldList VARCHAR(2000), --显示列名，如果是全部字段则为*
@PrimaryKey VARCHAR(100), --单一主键或唯一值键
@Where VARCHAR(2000), --查询条件 不含where字符，如id>10 and len(userid)>9
@Order VARCHAR(1000), --排序 不含order by字符，如id asc,userid desc，必须指定asc或desc
--注意当@SortType=3时生效，记住一定要在最后加上主键，否则会让你比较郁闷
@SortType INT, --排序规则 1:正序asc 2:倒序desc 3:多列排序方法
@RecorderCount INT, --记录总数 0:会返回总记录
@PageSize INT, --每页输出的记录数
@PageIndex INT, --当前页数
@TotalCount INT OUTPUT , --记返回总记录
@TotalPageCount INT OUTPUT --返回总页数
AS
SET NOCOUNT ON
IF ISNULL(@TotalCount,) = SET @TotalCount = 0
SET @Order = RTRIM(LTRIM(@Order))
SET @PrimaryKey = RTRIM(LTRIM(@PrimaryKey))
SET @FieldList = REPLACE(RTRIM(LTRIM(@FieldList)), ,)
WHILE CHARINDEX(, ,@Order) > 0 or CHARINDEX( ,,@Order) > 0
BEGIN
SET @Order = REPLACE(@Order,, ,,)
SET @Order = REPLACE(@Order, ,,,)
END
IF ISNULL(@TableName,) = or ISNULL(@FieldList,) =
or ISNULL(@PrimaryKey,) =
or @SortType < 1 or @SortType >3
or @RecorderCount < 0 or @PageSize < 0 or @PageIndex < 0
BEGIN
PRINT(ERR_00)
RETURN
END
IF @SortType = 3
BEGIN
IF (UPPER(RIGHT(@Order,4))!= ASC AND UPPER(RIGHT(@Order,5))!= DESC)
BEGIN PRINT(ERR_02) RETURN END
END
DECLARE @new_where1 VARCHAR(1000)
DECLARE @new_where2 VARCHAR(1000)
DECLARE @new_order1 VARCHAR(1000)
DECLARE @new_order2 VARCHAR(1000)
DECLARE @new_order3 VARCHAR(1000)
DECLARE @Sql VARCHAR(8000)
DECLARE @SqlCount NVARCHAR(4000)
IF ISNULL(@where,) =
BEGIN
SET @new_where1 =
SET @new_where2 = Where
END
ELSE
BEGIN
SET @new_where1 = Where + @where
SET @new_where2 = Where + @where + AND
END
IF ISNULL(@order,) = or @SortType = 1 or @SortType = 2
BEGIN
IF @SortType = 1
BEGIN
SET @new_order1 = orDER BY + @PrimaryKey + ASC
SET @new_order2 = orDER BY + @PrimaryKey + DESC
END
IF @SortType = 2
BEGIN
SET @new_order1 = orDER BY + @PrimaryKey + DESC
SET @new_order2 = orDER BY + @PrimaryKey + ASC
END
END
ELSE
BEGIN
SET @new_order1 = orDER BY + @Order
END
IF @SortType = 3 AND CHARINDEX(,+@PrimaryKey+ ,,+@Order)>0
BEGIN
SET @new_order1 = orDER BY + @Order
SET @new_order2 = @Order + ,
SET @new_order2 = REPLACE(REPLACE(@new_order2,ASC,,{ASC},),DESC,,{DESC},)
SET @new_order2 = REPLACE(REPLACE(@new_order2,{ASC},,DESC,),{DESC},,ASC,)
SET @new_order2 = orDER BY + SUBSTRING(@new_order2,1,LEN(@new_order2)-1)
IF @FieldList <> *
BEGIN
SET @new_order3 = REPLACE(REPLACE(@Order + ,,ASC,,,),DESC,,,)
SET @FieldList = , + @FieldList
WHILE CHARINDEX(,,@new_order3)>0
BEGIN
IF CHARINDEX(SUBSTRING(,+@new_order3,1,CHARINDEX(,,@new_order3)),,+@FieldList+,)>0
BEGIN
SET @FieldList =
@FieldList + , + SUBSTRING(@new_order3,1,CHARINDEX(,,@new_order3))
END
SET @new_order3 =
SUBSTRING(@new_order3,CHARINDEX(,,@new_order3)+1,LEN(@new_order3))
END
SET @FieldList = SUBSTRING(@FieldList,2,LEN(@FieldList))
END
END
SET @SqlCount = Select @TotalCount=COUNT(*),@TotalPageCount=CEILING((COUNT(*)+0.0)/
+ CAST(@PageSize AS VARCHAR)+) FROM (Select * FROM + @TableName + @new_where1+) AS T
IF @RecorderCount = 0
BEGIN
EXEC SP_EXECUTESQL @SqlCount,N@TotalCount INT OUTPUT,@TotalPageCount INT OUTPUT,
@TotalCount OUTPUT,@TotalPageCount OUTPUT
END
ELSE
BEGIN
Select @TotalCount = @RecorderCount
END
IF @PageIndex > CEILING((@TotalCount+0.0)/@PageSize)
BEGIN
SET @PageIndex = CEILING((@TotalCount+0.0)/@PageSize)
END
IF @PageIndex = 1 or @PageIndex >= CEILING((@TotalCount+0.0)/@PageSize)
BEGIN
IF @PageIndex = 1 --返回第一页数据
BEGIN
SET @Sql = Select * FROM (Select TOP + STR(@PageSize) + + @FieldList + FROM
+ @TableName + @new_where1 + @new_order1 +) AS TMP + @new_order1
END
IF @PageIndex >= CEILING((@TotalCount+0.0)/@PageSize) --返回最后一页数据
BEGIN
SET @Sql = Select TOP + STR(@PageSize) + + @FieldList + FROM (
+ Select TOP + STR(ABS(@PageSize*@PageIndex-@TotalCount-@PageSize))
+ + @FieldList + FROM
+ @TableName + @new_where1 + @new_order2 + ) AS TMP
+ @new_order1
END
END
ELSE
BEGIN
IF @SortType = 1 --仅主键正序排序
BEGIN
IF @PageIndex <= CEILING((@TotalCount+0.0)/@PageSize)/2 --正向检索
BEGIN
SET @Sql = Select TOP + STR(@PageSize) + + @FieldList + FROM
+ @TableName + @new_where2 + @PrimaryKey + >
+ (Select MAX( + @PrimaryKey + ) FROM (Select TOP
+ STR(@PageSize*(@PageIndex-1)) + + @PrimaryKey
+ FROM + @TableName
+ @new_where1 + @new_order1 + ) AS TMP) + @new_order1
END
ELSE --反向检索
BEGIN
SET @Sql = Select TOP + STR(@PageSize) + + @FieldList + FROM (
+ Select TOP + STR(@PageSize) +
+ @FieldList + FROM
+ @TableName + @new_where2 + @PrimaryKey + <
+ (Select MIN( + @PrimaryKey + ) FROM (Select TOP
+ STR(@TotalCount-@PageSize*@PageIndex) + + @PrimaryKey
+ FROM + @TableName
+ @new_where1 + @new_order2 + ) AS TMP) + @new_order2
+ ) AS TMP + @new_order1
END
END
IF @SortType = 2 --仅主键反序排序
BEGIN
IF @PageIndex <= CEILING((@TotalCount+0.0)/@PageSize)/2 --正向检索
BEGIN
SET @Sql = Select TOP + STR(@PageSize) + + @FieldList + FROM
+ @TableName + @new_where2 + @PrimaryKey + <
+ (Select MIN( + @PrimaryKey + ) FROM (Select TOP
+ STR(@PageSize*(@PageIndex-1)) + + @PrimaryKey
+ FROM + @TableName
+ @new_where1 + @new_order1 + ) AS TMP) + @new_order1
END
ELSE --反向检索
BEGIN
SET @Sql = Select TOP + STR(@PageSize) + + @FieldList + FROM (
+ Select TOP + STR(@PageSize) +
+ @FieldList + FROM
+ @TableName + @new_where2 + @PrimaryKey + >
+ (Select MAX( + @PrimaryKey + ) FROM (Select TOP
+ STR(@TotalCount-@PageSize*@PageIndex) + + @PrimaryKey
+ FROM + @TableName
+ @new_where1 + @new_order2 + ) AS TMP) + @new_order2
+ ) AS TMP + @new_order1
END
END
IF @SortType = 3 --多列排序，必须包含主键，且放置最后，否则不处理
BEGIN
IF CHARINDEX(, + @PrimaryKey + ,, + @Order) = 0
BEGIN PRINT(ERR_02) RETURN END
IF @PageIndex <= CEILING((@TotalCount+0.0)/@PageSize)/2 --正向检索
BEGIN
SET @Sql = Select TOP + STR(@PageSize) + + @FieldList + FROM (
+ Select TOP + STR(@PageSize) + + @FieldList + FROM (
+ Select TOP + STR(@PageSize*@PageIndex) + + @FieldList
+ FROM + @TableName + @new_where1 + @new_order1 + ) AS TMP
+ @new_order2 + ) AS TMP + @new_order1
END
ELSE --反向检索
BEGIN
SET @Sql = Select TOP + STR(@PageSize) + + @FieldList + FROM (
+ Select TOP + STR(@PageSize) + + @FieldList + FROM (
+ Select TOP + STR(@TotalCount-@PageSize *@PageIndex+@PageSize) + + @FieldList
+ FROM + @TableName + @new_where1 + @new_order2 + ) AS TMP
+ @new_order1 + ) AS TMP + @new_order1
END
END
END
PRINT(@SQL)
EXEC(@Sql)

USE [CaiLi]
GO
/****** Object: StoredProcedure [dbo].[SqlPagination] Script Date: 10/26/2011 11:40:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SqlPagination]
/*
***************************************************************
** 千万数量级分页存储过程 **
***************************************************************
参数说明:
1.Tables :表名称,视图
2.PrimaryKey :主关键字
3.Sort :排序语句，不带Order By 比如：NewsID Desc,OrderRows Asc
4.CurrentPage :当前页码
5.PageSize :分页尺寸
6.Filter :过滤语句，不带Where
7.Group :Group语句,不带Group By
***************************************************************/
(
@Tables varchar(1000),
@PrimaryKey varchar(100),
@Sort varchar(200) = NULL,
@CurrentPage int = 1,
@PageSize int = 10,
@Fields varchar(1000) = *,
@Filter varchar(1000) = NULL,
@Group varchar(1000) = NULL
)
AS
/*默认排序*/
if @PrimaryKey IS NULL or @PrimaryKey =
set @PrimaryKey=ID
IF @Sort IS NULL or @Sort =
SET @Sort = @PrimaryKey
IF @Fields IS NULL or @Fields =
SET @Fields = *
DECLARE @SortTable varchar(100)
DECLARE @SortName varchar(100)
DECLARE @strSortColumn varchar(200)
DECLARE @operator char(2)
DECLARE @type varchar(100)
DECLARE @prec int
/*设定排序语句.*/
if charindex(,,@Sort) >0
set @strSortColumn = substring(@Sort,0,charindex(,,@Sort))
else
set @strSortColumn = @Sort
IF CHARINDEX(DESC,@Sort)>0
BEGIN
SET @strSortColumn = REPLACE(@strSortColumn, DESC, )
SET @operator = <=
END
ELSE
BEGIN
IF CHARINDEX(ASC,@Sort)> 0
BEGIN
SET @strSortColumn = REPLACE(@strSortColumn, ASC, )
SET @operator = >=
END
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)
DECLARE @strSort varchar(200)
/*默认当前页*/
IF @CurrentPage < 1
SET @CurrentPage = 1
/*设置分页参数.*/
SET @strPageSize = CAST(@PageSize AS varchar(50))
SET @strStartRow = CAST(((@CurrentPage - 1)*@PageSize + 1) AS varchar(50))
/*筛选以及分组语句.*/
IF @Filter IS NOT NULL AND @Filter !=
BEGIN
SET @strFilter = Where 1=1 + @Filter +
SET @strSimpleFilter =@Filter +
END
ELSE
BEGIN
SET @strSimpleFilter =
SET @strFilter =
END
IF @Group IS NOT NULL AND @Group !=
SET @strGroup = GROUP BY + @Group +
ELSE
SET @strGroup =
IF @Sort IS NOT NULL AND @Sort !=
SET @strSort = ORDER BY + @Sort +
ELSE
SET @strSort =
--print(Select + @Fields + FROM + (Select *,ROW_NUMBER() OVER (+@strSort+)as RowNumber FROM +@Tables+) t + Where t.RowNumber between +@strStartRow+ and + + @strSimpleFilter + + @strSort + @strGroup)
/*执行查询语句*/
declare @STRORDER varchar(50)
if CHARINDEX(,,@strSort)>0
set @STRORDER=SUBSTRING(@strSort, 0, CHARINDEX(,,@strSort))
else
set @STRORDER=@strSort
EXEC(
DECLARE @SortColumn + @type +
DECLARE @TotalCount int
DECLARE @ENDCOUNT int
DECLARE @strENDCOUNT varchar(50)
--Select count(1) FROM + @Tables + @strFilter+
set @TotalCount=(Select count(1) FROM + @Tables + @strFilter+)+
SET ROWCOUNT + @strStartRow +
SET @ENDCOUNT=CAST(+@strStartRow+ AS int)+CAST(+@strPageSize+ AS int)-1
IF @ENDCOUNT > @TotalCount
BEGIN
SET @ENDCOUNT = @TotalCount
END
set @strENDCOUNT=CAST(@ENDCOUNT AS varchar(50))
Select @SortColumn= + @strSortColumn + FROM + @Tables + @strFilter + + @strGroup + @strSort +
SET ROWCOUNT + @strPageSize +
Select + @Fields + FROM + (Select *,ROW_NUMBER() OVER (+@STRORDER+)as RowNumber FROM +@Tables+ where 1=1 +@strSimpleFilter+) t + Where t.RowNumber between +@strStartRow+ and @strENDCOUNT + @strGroup + @strSort + )
GO