sqlserver存储过程分页代码

declare@TotalCountint
declare@TotalPageCountint
execP_viewPage_A"type1","*","id","","idasc",1,0,4,3,@TotalCountoutput,@TotalPageCountoutput

select*fromtype1


CreatePROCP_viewPage_A
/*
nzperfect[no_mIss]高效通用分页存储过程(双向检索)2007.5.7QQ:34813284
敬告：适用于单一主键或存在唯一值列的表或视图
ps:Sql语句为8000字节,调用时请注意传入参数及sql总长度不要超过指定范围
*/
@TableNameVARCHAR(200),--表名
@FieldListVARCHAR(2000),--显示列名，如果是全部字段则为*
@PrimaryKeyVARCHAR(100),--单一主键或唯一值键
@WhereVARCHAR(2000),--查询条件不含"where"字符，如id>10andlen(userid)>9
@OrderVARCHAR(1000),--排序不含"orderby"字符，如idasc,useriddesc，必须指定asc或desc
--注意当@SortType=3时生效，记住一定要在最后加上主键，否则会让你比较郁闷
@SortTypeINT,--排序规则1:正序asc2:倒序desc3:多列排序方法
@RecorderCountINT,--记录总数0:会返回总记录
@PageSizeINT,--每页输出的记录数
@PageIndexINT,--当前页数
@TotalCountINTOUTPUT,--记返回总记录
@TotalPageCountINTOUTPUT--返回总页数
AS
SETNOCOUNTON
IFISNULL(@TotalCount,"")=""SET@TotalCount=0
SET@Order=RTRIM(LTRIM(@Order))
SET@PrimaryKey=RTRIM(LTRIM(@PrimaryKey))
SET@FieldList=REPLACE(RTRIM(LTRIM(@FieldList)),"","")
WHILECHARINDEX(",",@Order)>0ORCHARINDEX(",",@Order)>0
BEGIN
SET@Order=REPLACE(@Order,",",",")
SET@Order=REPLACE(@Order,",",",")
END
IFISNULL(@TableName,"")=""ORISNULL(@FieldList,"")=""
ORISNULL(@PrimaryKey,"")=""
OR@SortType<1OR@SortType>3
OR@RecorderCount<0OR@PageSize<0OR@PageIndex<0
BEGIN
PRINT("ERR_00")
RETURN
END
IF@SortType=3
BEGIN
IF(UPPER(RIGHT(@Order,4))!="ASC"ANDUPPER(RIGHT(@Order,5))!="DESC")
BEGINPRINT("ERR_02")RETURNEND
END
DECLARE@new_where1VARCHAR(1000)
DECLARE@new_where2VARCHAR(1000)
DECLARE@new_order1VARCHAR(1000)
DECLARE@new_order2VARCHAR(1000)
DECLARE@new_order3VARCHAR(1000)
DECLARE@SqlVARCHAR(8000)
DECLARE@SqlCountNVARCHAR(4000)
IFISNULL(@where,"")=""
BEGIN
SET@new_where1=""
SET@new_where2="WHERE"
END
ELSE
BEGIN
SET@new_where1="WHERE"+@where
SET@new_where2="WHERE"+@where+"AND"
END
IFISNULL(@order,"")=""OR@SortType=1OR@SortType=2
BEGIN
IF@SortType=1
BEGIN
SET@new_order1="ORDERBY"+@PrimaryKey+"ASC"
SET@new_order2="ORDERBY"+@PrimaryKey+"DESC"
END
IF@SortType=2
BEGIN
SET@new_order1="ORDERBY"+@PrimaryKey+"DESC"
SET@new_order2="ORDERBY"+@PrimaryKey+"ASC"
END
END
ELSE
BEGIN
SET@new_order1="ORDERBY"+@Order
END
IF@SortType=3ANDCHARINDEX(","+@PrimaryKey+"",","+@Order)>0
BEGIN
SET@new_order1="ORDERBY"+@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="ORDERBY"+SUBSTRING(@new_order2,1,LEN(@new_order2)-1)
IF@FieldList<>"*"
BEGIN
SET@new_order3=REPLACE(REPLACE(@Order+",","ASC,",","),"DESC,",",")
SET@FieldList=","+@FieldList
WHILECHARINDEX(",",@new_order3)>0
BEGIN
IFCHARINDEX(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(@PageSizeASVARCHAR)+")FROM"+@TableName+@new_where1
IF@RecorderCount=0
BEGIN
EXECSP_EXECUTESQL@SqlCount,N"@TotalCountINTOUTPUT,@TotalPageCountINTOUTPUT",
@TotalCountOUTPUT,@TotalPageCountOUTPUT
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=1OR@PageIndex>=CEILING((@TotalCount+0.0)/@PageSize)
BEGIN
IF@PageIndex=1--返回第一页数据
BEGIN
SET@Sql="SELECTTOP"+STR(@PageSize)+""+@FieldList+"FROM"
+@TableName+@new_where1+@new_order1
END
IF@PageIndex>=CEILING((@TotalCount+0.0)/@PageSize)--返回最后一页数据
BEGIN
SET@Sql="SELECTTOP"+STR(@PageSize)+""+@FieldList+"FROM("
+"SELECTTOP"+STR(ABS(@PageSize*@PageIndex-@TotalCount-@PageSize))
+""+@FieldList+"FROM"
+@TableName+@new_where1+@new_order2+")ASTMP"
+@new_order1
END
END
ELSE
BEGIN
IF@SortType=1--仅主键正序排序
BEGIN
IF@PageIndex<=CEILING((@TotalCount+0.0)/@PageSize)/2--正向检索
BEGIN
SET@Sql="SELECTTOP"+STR(@PageSize)+""+@FieldList+"FROM"
+@TableName+@new_where2+@PrimaryKey+">"
+"(SELECTMAX("+@PrimaryKey+")FROM(SELECTTOP"
+STR(@PageSize*(@PageIndex-1))+""+@PrimaryKey
+"FROM"+@TableName
+@new_where1+@new_order1+")ASTMP)"+@new_order1
END
ELSE--反向检索
BEGIN
SET@Sql="SELECTTOP"+STR(@PageSize)+""+@FieldList+"FROM("
+"SELECTTOP"+STR(@PageSize)+""
+@FieldList+"FROM"
+@TableName+@new_where2+@PrimaryKey+"<"
+"(SELECTMIN("+@PrimaryKey+")FROM(SELECTTOP"
+STR(@TotalCount-@PageSize*@PageIndex)+""+@PrimaryKey
+"FROM"+@TableName
+@new_where1+@new_order2+")ASTMP)"+@new_order2
+")ASTMP"+@new_order1
END
END
IF@SortType=2--仅主键反序排序
BEGIN
IF@PageIndex<=CEILING((@TotalCount+0.0)/@PageSize)/2--正向检索
BEGIN
SET@Sql="SELECTTOP"+STR(@PageSize)+""+@FieldList+"FROM"
+@TableName+@new_where2+@PrimaryKey+"<"
+"(SELECTMIN("+@PrimaryKey+")FROM(SELECTTOP"
+STR(@PageSize*(@PageIndex-1))+""+@PrimaryKey
+"FROM"+@TableName
+@new_where1+@new_order1+")ASTMP)"+@new_order1
END
ELSE--反向检索
BEGIN
SET@Sql="SELECTTOP"+STR(@PageSize)+""+@FieldList+"FROM("
+"SELECTTOP"+STR(@PageSize)+""
+@FieldList+"FROM"
+@TableName+@new_where2+@PrimaryKey+">"
+"(SELECTMAX("+@PrimaryKey+")FROM(SELECTTOP"
+STR(@TotalCount-@PageSize*@PageIndex)+""+@PrimaryKey
+"FROM"+@TableName
+@new_where1+@new_order2+")ASTMP)"+@new_order2
+")ASTMP"+@new_order1
END
END
IF@SortType=3--多列排序，必须包含主键，且放置最后，否则不处理
BEGIN
IFCHARINDEX(","+@PrimaryKey+"",","+@Order)=0
BEGINPRINT("ERR_02")RETURNEND
IF@PageIndex<=CEILING((@TotalCount+0.0)/@PageSize)/2--正向检索
BEGIN
SET@Sql="SELECTTOP"+STR(@PageSize)+""+@FieldList+"FROM("
+"SELECTTOP"+STR(@PageSize)+""+@FieldList+"FROM("
+"SELECTTOP"+STR(@PageSize*@PageIndex)+""+@FieldList
+"FROM"+@TableName+@new_where1+@new_order1+")ASTMP"
+@new_order2+")ASTMP"+@new_order1
END
ELSE--反向检索
BEGIN
SET@Sql="SELECTTOP"+STR(@PageSize)+""+@FieldList+"FROM("
+"SELECTTOP"+STR(@PageSize)+""+@FieldList+"FROM("
+"SELECTTOP"+STR(@TotalCount-@PageSize*@PageIndex+@PageSize)+""+@FieldList
+"FROM"+@TableName+@new_where1+@new_order2+")ASTMP"
+@new_order1+")ASTMP"+@new_order1
END
END
END
EXEC(@Sql)
GO