通用sqlserver分页存储过程
通用sqlserver分页存储过程
来自：http://www.cnblogs.com/nzperfect/archive/2007/05/08/738999.html
单主键：
CREATEPROCP_viewPage
/**//*
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
PRINT(@Sql)
EXEC(@Sql)
GO
联合主键的：
CREATEPROCP_public_ViewPage
/**//*
no_mIss通用分页存储过程2007.3.1QQ:34813284
适用于联合主键/单主键/存在能确定唯一行列/存在能确定唯一行的多列(用英文,隔开)
调用：
第一页查询时返回总记录和总页数及第一页记录：
EXECUTEP_public_ViewPage_per'TableName','col1,col2,col3,col4','pk1,pk2,pk3',
'col5>0andcol7<9','pk1asc,pk2asc,pk3asc',0,10,1,
@TotalCountOUTPUT,@TotalPageCountOUTPUT
其它页调用，比如第89页(假设第一页查询时返回总记录为2000000)：
EXECUTEP_public_ViewPage_per'TableName','col1,col2,col3,col4','pk1,pk2,pk3',
'col5>0andcol7<9','pk1asc,pk2asc,pk3asc',2000000,10,89,
@TotalCountOUTPUT,@TotalPageCountOUTPUT
*/
@TableNameVARCHAR(200),--表名
@FieldListVARCHAR(2000),--显示列名
@PrimaryKeyVARCHAR(100),--单一主键或唯一值键或联合主键列表(用英文,隔开)或能确定唯一行的多列列表(用英文,隔开)
@WhereVARCHAR(1000),--查询条件不含'where'字符
@OrderVARCHAR(1000),--排序不含'orderby'字符,用英文,隔开
@RecorderCountINT,--记录总数0:会返回总记录
@PageSizeINT,--每页输出的记录数
@PageIndexINT,--当前页数
@TotalCountINTOUTPUT,--返回记录总数
@TotalPageCountINTOUTPUT--返回总页数
AS
SETNOCOUNTON
SET@FieldList=REPLACE(@FieldList,'','')
IF@FieldList='*'
BEGINSET@FieldList='A.*'END
ELSE
BEGIN
SET@FieldList='A.'+REPLACE(@FieldList,',',',A.')
END
WHILECHARINDEX(',',@Order)>0
BEGIN
SET@Order=REPLACE(@Order,',',',')
END
IFISNULL(@TableName,'')=''ORISNULL(@PrimaryKey,'')=''
OR@RecorderCount<0OR@PageSize<0OR@PageIndex<0
BEGIN
RETURN
END
DECLARE@new_where1VARCHAR(1000)
DECLARE@new_where2VARCHAR(1000)
DECLARE@new_where3VARCHAR(1000)
DECLARE@new_where4VARCHAR(1000)
DECLARE@new_order1VARCHAR(1000)
DECLARE@new_order2VARCHAR(1000)
DECLARE@FieldsVARCHAR(1000)
DECLARE@SqlVARCHAR(8000)
DECLARE@SqlCountNVARCHAR(4000)
SET@Fields=@PrimaryKey+','
SET@new_where2=''
SET@new_where4=''
IFISNULL(@where,'')=''
BEGIN
SET@new_where1=''
SET@new_where3='WHERE'
END
ELSE
BEGIN
SET@new_where1='WHERE'+@where+''
SET@new_where3='WHERE1=1'
+REPLACE('AND'+@where,'AND','ANDA.')+'AND'
END
WHILECHARINDEX(',',@Fields)>0
BEGIN
SET@new_where2=@new_where2
+'A.'+LTRIM(LEFT(@Fields,CHARINDEX(',',@Fields)-1))
+'=B.'+LTRIM(LEFT(@Fields,CHARINDEX(',',@Fields)-1))+'AND'
SET@new_where4=@new_where4
+'B.'+LTRIM(LEFT(@Fields,CHARINDEX(',',@Fields)-1))+'ISNULLAND'
SET@Fields=SUBSTRING(@Fields,CHARINDEX(',',@Fields)+1,LEN(@Fields))
END
SET@new_where2=LEFT(@new_where2,LEN(@new_where2)-4)
SET@new_where4=LEFT(@new_where4,LEN(@new_where4)-4)
IFISNULL(@order,'')=''
BEGIN
SET@new_order1=''
SET@new_order2=''
END
ELSE
BEGIN
SET@new_order1='ORDERBY'+@Order
SET@new_order2='ORDERBY'
+RIGHT(REPLACE(','+@Order,',',',A.'),
LEN(REPLACE(','+@Order,',',',A.'))-1)
END
SET@SqlCount='SELECT@TotalCount=COUNT(*),@TotalPageCount=CEILING((COUNT(*)+0.0)/'
+CAST(@PageSizeASVARCHAR)+')FROM'+@TableName
+'A'+@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=1
BEGIN
SET@Sql='SELECTTOP'+STR(@PageSize)+''+@FieldList+'FROM'
+@TableName+'A'+@new_where1+@new_order1
END
ELSE
BEGIN
SET@Sql='SELECTTOP'+STR(@PageSize)+''+@FieldList+'FROM'
+@TableName+'ALEFTJOIN(SELECTTOP'
+STR(@PageSize*(@PageIndex-1))
+''+@PrimaryKey+'FROM'+@TableName+@new_where1
+@new_order1+')BON'+@new_where2+@new_where3
+@new_where4+@new_order2
END
EXEC(@Sql)
GO 优质内容筛选与推荐>>
1、Swing编程把图片放入frame里。先不作为背景图片
2、C#使用DISKID32.DLL读取硬盘序列号
3、「日常训练&知识学习」树的分块（王室联邦，HYSBZ-1086）
4、第四次作业——选择结构（2）
5、第3章 表达式
CREATEPROCP_viewPage
/**//*
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
PRINT(@Sql)
EXEC(@Sql)
GO
联合主键的：
CREATEPROCP_public_ViewPage
/**//*
no_mIss通用分页存储过程2007.3.1QQ:34813284
适用于联合主键/单主键/存在能确定唯一行列/存在能确定唯一行的多列(用英文,隔开)
调用：
第一页查询时返回总记录和总页数及第一页记录：
EXECUTEP_public_ViewPage_per'TableName','col1,col2,col3,col4','pk1,pk2,pk3',
'col5>0andcol7<9','pk1asc,pk2asc,pk3asc',0,10,1,
@TotalCountOUTPUT,@TotalPageCountOUTPUT
其它页调用，比如第89页(假设第一页查询时返回总记录为2000000)：
EXECUTEP_public_ViewPage_per'TableName','col1,col2,col3,col4','pk1,pk2,pk3',
'col5>0andcol7<9','pk1asc,pk2asc,pk3asc',2000000,10,89,
@TotalCountOUTPUT,@TotalPageCountOUTPUT
*/
@TableNameVARCHAR(200),--表名
@FieldListVARCHAR(2000),--显示列名
@PrimaryKeyVARCHAR(100),--单一主键或唯一值键或联合主键列表(用英文,隔开)或能确定唯一行的多列列表(用英文,隔开)
@WhereVARCHAR(1000),--查询条件不含'where'字符
@OrderVARCHAR(1000),--排序不含'orderby'字符,用英文,隔开
@RecorderCountINT,--记录总数0:会返回总记录
@PageSizeINT,--每页输出的记录数
@PageIndexINT,--当前页数
@TotalCountINTOUTPUT,--返回记录总数
@TotalPageCountINTOUTPUT--返回总页数
AS
SETNOCOUNTON
SET@FieldList=REPLACE(@FieldList,'','')
IF@FieldList='*'
BEGINSET@FieldList='A.*'END
ELSE
BEGIN
SET@FieldList='A.'+REPLACE(@FieldList,',',',A.')
END
WHILECHARINDEX(',',@Order)>0
BEGIN
SET@Order=REPLACE(@Order,',',',')
END
IFISNULL(@TableName,'')=''ORISNULL(@PrimaryKey,'')=''
OR@RecorderCount<0OR@PageSize<0OR@PageIndex<0
BEGIN
RETURN
END
DECLARE@new_where1VARCHAR(1000)
DECLARE@new_where2VARCHAR(1000)
DECLARE@new_where3VARCHAR(1000)
DECLARE@new_where4VARCHAR(1000)
DECLARE@new_order1VARCHAR(1000)
DECLARE@new_order2VARCHAR(1000)
DECLARE@FieldsVARCHAR(1000)
DECLARE@SqlVARCHAR(8000)
DECLARE@SqlCountNVARCHAR(4000)
SET@Fields=@PrimaryKey+','
SET@new_where2=''
SET@new_where4=''
IFISNULL(@where,'')=''
BEGIN
SET@new_where1=''
SET@new_where3='WHERE'
END
ELSE
BEGIN
SET@new_where1='WHERE'+@where+''
SET@new_where3='WHERE1=1'
+REPLACE('AND'+@where,'AND','ANDA.')+'AND'
END
WHILECHARINDEX(',',@Fields)>0
BEGIN
SET@new_where2=@new_where2
+'A.'+LTRIM(LEFT(@Fields,CHARINDEX(',',@Fields)-1))
+'=B.'+LTRIM(LEFT(@Fields,CHARINDEX(',',@Fields)-1))+'AND'
SET@new_where4=@new_where4
+'B.'+LTRIM(LEFT(@Fields,CHARINDEX(',',@Fields)-1))+'ISNULLAND'
SET@Fields=SUBSTRING(@Fields,CHARINDEX(',',@Fields)+1,LEN(@Fields))
END
SET@new_where2=LEFT(@new_where2,LEN(@new_where2)-4)
SET@new_where4=LEFT(@new_where4,LEN(@new_where4)-4)
IFISNULL(@order,'')=''
BEGIN
SET@new_order1=''
SET@new_order2=''
END
ELSE
BEGIN
SET@new_order1='ORDERBY'+@Order
SET@new_order2='ORDERBY'
+RIGHT(REPLACE(','+@Order,',',',A.'),
LEN(REPLACE(','+@Order,',',',A.'))-1)
END
SET@SqlCount='SELECT@TotalCount=COUNT(*),@TotalPageCount=CEILING((COUNT(*)+0.0)/'
+CAST(@PageSizeASVARCHAR)+')FROM'+@TableName
+'A'+@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=1
BEGIN
SET@Sql='SELECTTOP'+STR(@PageSize)+''+@FieldList+'FROM'
+@TableName+'A'+@new_where1+@new_order1
END
ELSE
BEGIN
SET@Sql='SELECTTOP'+STR(@PageSize)+''+@FieldList+'FROM'
+@TableName+'ALEFTJOIN(SELECTTOP'
+STR(@PageSize*(@PageIndex-1))
+''+@PrimaryKey+'FROM'+@TableName+@new_where1
+@new_order1+')BON'+@new_where2+@new_where3
+@new_where4+@new_order2
END
EXEC(@Sql)
GO 优质内容筛选与推荐>>
1、Swing编程把图片放入frame里。先不作为背景图片
2、C#使用DISKID32.DLL读取硬盘序列号
3、「日常训练&知识学习」树的分块（王室联邦，HYSBZ-1086）
4、第四次作业——选择结构（2）
5、第3章 表达式