两个存储过程的参数是一样的:
[Basic_Pagination2005]:只能在SQLServer2005下用;
[Basic_Pagination2000]:可在SQLServer2000和SQLServer2005下通用;
其中的参数说明在代码中已有注释。
需要注意的是当@IsReCount=1时,会返回记录总数。所以在.NET中需用DataSet存放记录集。
第一个Table是要查询的字段数据,第二个Table便是记录总数。
喜欢研究分页的朋友不妨一起讨论
1。Basic_Pagination2000
create
PROCEDURE
[
dbo
]
.
[
Basic_Pagination2000
]
@tblName varchar ( 255 ), -- 表名
@fidlelist varchar ( 2000 ), -- 要查询字段
@fldName varchar ( 255 ), -- 排序字段
@PageSize int , -- 页尺寸
@PageIndex int , -- 页码
@IsReCount bit , -- 返回记录总数, 非 0 值则返回
@OrderType bit , -- 设置排序类型, 非 0 值则降序
@strWhere varchar ( 1000 ) -- 查询条件 (注意: 不要加 where)
AS
declare @strSQL varchar ( 6000 ) -- 主语句
declare @strTmp varchar ( 100 ), @tmpwhere varchar ( 200 ), @tmpwhere2 varchar ( 200 ) -- 临时变量
declare @strOrder varchar ( 400 ) -- 排序类型
if @OrderType != 0
begin
set @strTmp = ' <(select min '
set @strOrder = ' order by [ ' + @fldName + ' ] desc '
end
else
begin
set @strTmp = ' >(select max '
set @strOrder = ' order by [ ' + @fldName + ' ] asc '
end
set @tmpwhere = '' ;
set @tmpwhere2 = '' ;
if ( @strWhere != '' )
begin
set @tmpwhere = ' and ' + @strWhere ;
set @tmpwhere2 = ' where ' + @strWhere ;
end
if @PageIndex = 1
begin
set @strSQL = ' select top ' + str ( @PageSize ) + ' ' + @fidlelist + ' ' + ' from [ '
+ @tblName + ' ] ' + @tmpwhere2 + ' ' + @strOrder
end
else
begin
set @strSQL = ' select top ' + str ( @PageSize ) + ' ' + @fidlelist + ' ' + ' from [ '
+ @tblName + ' ] where [ ' + @fldName + ' ] ' + @strTmp + ' ([ '
+ @fldName + ' ]) from (select top ' + str (( @PageIndex - 1 ) * @PageSize ) + ' [ '
+ @fldName + ' ] from [ ' + @tblName + ' ] ' + @tmpwhere2 + ' '
+ @strOrder + ' ) as tblTmp) ' + @tmpwhere + ' ' + @strOrder
end
print @strSQL
exec ( @strSQL )
if @IsReCount != 0
begin
set @strSQL = ' select count(*) as Total from ' + @tblName + ' ' + @tmpwhere2
exec ( @strSQL )
end
@tblName varchar ( 255 ), -- 表名
@fidlelist varchar ( 2000 ), -- 要查询字段
@fldName varchar ( 255 ), -- 排序字段
@PageSize int , -- 页尺寸
@PageIndex int , -- 页码
@IsReCount bit , -- 返回记录总数, 非 0 值则返回
@OrderType bit , -- 设置排序类型, 非 0 值则降序
@strWhere varchar ( 1000 ) -- 查询条件 (注意: 不要加 where)
AS
declare @strSQL varchar ( 6000 ) -- 主语句
declare @strTmp varchar ( 100 ), @tmpwhere varchar ( 200 ), @tmpwhere2 varchar ( 200 ) -- 临时变量
declare @strOrder varchar ( 400 ) -- 排序类型
if @OrderType != 0
begin
set @strTmp = ' <(select min '
set @strOrder = ' order by [ ' + @fldName + ' ] desc '
end
else
begin
set @strTmp = ' >(select max '
set @strOrder = ' order by [ ' + @fldName + ' ] asc '
end
set @tmpwhere = '' ;
set @tmpwhere2 = '' ;
if ( @strWhere != '' )
begin
set @tmpwhere = ' and ' + @strWhere ;
set @tmpwhere2 = ' where ' + @strWhere ;
end
if @PageIndex = 1
begin
set @strSQL = ' select top ' + str ( @PageSize ) + ' ' + @fidlelist + ' ' + ' from [ '
+ @tblName + ' ] ' + @tmpwhere2 + ' ' + @strOrder
end
else
begin
set @strSQL = ' select top ' + str ( @PageSize ) + ' ' + @fidlelist + ' ' + ' from [ '
+ @tblName + ' ] where [ ' + @fldName + ' ] ' + @strTmp + ' ([ '
+ @fldName + ' ]) from (select top ' + str (( @PageIndex - 1 ) * @PageSize ) + ' [ '
+ @fldName + ' ] from [ ' + @tblName + ' ] ' + @tmpwhere2 + ' '
+ @strOrder + ' ) as tblTmp) ' + @tmpwhere + ' ' + @strOrder
end
print @strSQL
exec ( @strSQL )
if @IsReCount != 0
begin
set @strSQL = ' select count(*) as Total from ' + @tblName + ' ' + @tmpwhere2
exec ( @strSQL )
end
2。Basic_Pagination2005
ALTER
PROCEDURE
[
dbo
]
.
[
Basic_Pagination2005
]
@tblName nvarchar ( 200 ), -- 表名
@fidlelist nvarchar ( 1000 ), -- 要查询字段
@fldName nvarchar ( 100 ), -- 排序字段
@PageSize int , -- 页尺寸
@PageIndex int , -- 页码
@IsReCount bit , -- 返回记录总数, 非 0 值则返回
@OrderType bit , -- 设置排序类型, 非 0 值则降序
@strWhere nvarchar ( 1000 ) -- 查询条件
AS
declare @sqlstr nvarchar ( 4000 ), @tmpwhere nvarchar ( 4000 ), @tmporder nvarchar ( 100 )
BEGIN
if @OrderType != 0
begin
set @tmporder = @fldName + ' desc '
end
else
begin
set @tmporder = @fldName + ' asc '
end
set @tmpwhere = '' ;
if ( @strWhere != '' )
begin
set @tmpwhere = ' where ' + @strWhere ;
end
set @sqlstr = N ' select * from(select ' + @fidlelist + ' , ROW_NUMBER() OVER(order by ' + @tmporder + ' ) as row from ' + @tblName + @tmpwhere + ' ) tmp where row between ' + cast ((( @PageIndex - 1 ) * @PageSize + 1 ) as nvarchar ) + ' and ' + cast ( @PageIndex * @PageSize as nvarchar );
exec sp_executesql @sqlstr
if @IsReCount != 0
begin
set @sqlstr = N ' select count(*) as Total from ' + @tblName + @tmpwhere
exec sp_executesql @sqlstr
end
END
@tblName nvarchar ( 200 ), -- 表名
@fidlelist nvarchar ( 1000 ), -- 要查询字段
@fldName nvarchar ( 100 ), -- 排序字段
@PageSize int , -- 页尺寸
@PageIndex int , -- 页码
@IsReCount bit , -- 返回记录总数, 非 0 值则返回
@OrderType bit , -- 设置排序类型, 非 0 值则降序
@strWhere nvarchar ( 1000 ) -- 查询条件
AS
declare @sqlstr nvarchar ( 4000 ), @tmpwhere nvarchar ( 4000 ), @tmporder nvarchar ( 100 )
BEGIN
if @OrderType != 0
begin
set @tmporder = @fldName + ' desc '
end
else
begin
set @tmporder = @fldName + ' asc '
end
set @tmpwhere = '' ;
if ( @strWhere != '' )
begin
set @tmpwhere = ' where ' + @strWhere ;
end
set @sqlstr = N ' select * from(select ' + @fidlelist + ' , ROW_NUMBER() OVER(order by ' + @tmporder + ' ) as row from ' + @tblName + @tmpwhere + ' ) tmp where row between ' + cast ((( @PageIndex - 1 ) * @PageSize + 1 ) as nvarchar ) + ' and ' + cast ( @PageIndex * @PageSize as nvarchar );
exec sp_executesql @sqlstr
if @IsReCount != 0
begin
set @sqlstr = N ' select count(*) as Total from ' + @tblName + @tmpwhere
exec sp_executesql @sqlstr
end
END