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))!=ASCANDUPPER(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