create procedure SqlDataPaging
@tbName varchar(255), --表名
@tbFields varchar(1000), --返回字段
@OrderField varchar(255), --排序的字段名
@PageSize int, --页尺寸
@PageIndex int, --页码
@OrderType bit, --排序类型,1是升序,0是降序
@strWhere varchar(1500), --查询条件
@Total int output --返回总记录数
as
declare @strSql varchar(5000) --主语句
declare @strOrder varchar(200) --排序
declare @strSqlCount nvarchar(500)--查询记录总数主语句
declare @strtemp varchar(50) --排序附加语句
--------------排序-----------------0是降序,1未升序
set @Total=0
if @OrderType !=0
begin
set @strtemp= '>(select max('
set @strOrder='order by ' + @OrderField + ' asc '
end
else
begin
set @strtemp= '<(select min('
set @strOrder='order by '+ @OrderField + ' desc'
end
--------------总记录数---------------
if @strWhere !=''
begin
set @strSqlCount='Select @TotalCout=count(*) from ' + @tbName + ' where '+ @strWhere
end
else
begin
set @strSqlCount='Select @TotalCout=count(*) from ' + @tbName
endexec sp_executesql @strSqlCount,N'@TotalCout int output',@Total outputif @pageindex>(@Total/@PageSize+1)
set @pageindex=(@Total/@PageSize+1)else if @pageindex<0
set @pageindex=0
if @pageindex>-1
--------------分页为第一页------------
begin
if @PageIndex=0
begin
-- set @PageIndex =0
if @strWhere !=''
begin
set @strSql = 'Select top' +str(@PageSize)+ ' ' + @tbFields + ' from '+ @tbName +' where ' + @strWhere + ' ' + @strOrder
end
else
begin
set @strSql = 'Select top' +str(@PageSize)+ ' ' + @tbFields + ' from '+ @tbName + ' ' + @strOrder
end
end
--------------分页不为第一页------------
else
begin
if @strWhere !=''
begin
set @strSql='Select top ' +str(@PageSize) + ' ' + @tbFields
+' from ' + @tbName + ' where '+@OrderField +' '
+ @strtemp+ ' ' +@OrderField+ ') from (select top '
+str(@PageIndex*@PageSize) + ' '+@OrderField +' from '+
@tbName+' where '+@strwhere + ' '+ @strOrder +') as tb) and '+@strwhere+' '+ @strOrder
end
else
begin
set @strSql='Select top ' + str(@PageSize) + ' ' + @tbFields
+' from ' + @tbName + ' where '+@OrderField + ' '
+ @strtemp+ ' ' +@OrderField+ ') from (select top'
+str(@PageIndex*@PageSize) + ' '+@OrderField +' from '+@tbName
+ ' '+ @strOrder +') as tb)'+ @strOrder
end
end
exec (@strSql)
end else
begin
print 'wrong page'
end大家觉得这种分页怎样?我觉得挺臃肿的(语句)上.大家有什么好的分页方法SQL ?
@tbName varchar(255), --表名
@tbFields varchar(1000), --返回字段
@OrderField varchar(255), --排序的字段名
@PageSize int, --页尺寸
@PageIndex int, --页码
@OrderType bit, --排序类型,1是升序,0是降序
@strWhere varchar(1500), --查询条件
@Total int output --返回总记录数
as
declare @strSql varchar(5000) --主语句
declare @strOrder varchar(200) --排序
declare @strSqlCount nvarchar(500)--查询记录总数主语句
declare @strtemp varchar(50) --排序附加语句
--------------排序-----------------0是降序,1未升序
set @Total=0
if @OrderType !=0
begin
set @strtemp= '>(select max('
set @strOrder='order by ' + @OrderField + ' asc '
end
else
begin
set @strtemp= '<(select min('
set @strOrder='order by '+ @OrderField + ' desc'
end
--------------总记录数---------------
if @strWhere !=''
begin
set @strSqlCount='Select @TotalCout=count(*) from ' + @tbName + ' where '+ @strWhere
end
else
begin
set @strSqlCount='Select @TotalCout=count(*) from ' + @tbName
endexec sp_executesql @strSqlCount,N'@TotalCout int output',@Total outputif @pageindex>(@Total/@PageSize+1)
set @pageindex=(@Total/@PageSize+1)else if @pageindex<0
set @pageindex=0
if @pageindex>-1
--------------分页为第一页------------
begin
if @PageIndex=0
begin
-- set @PageIndex =0
if @strWhere !=''
begin
set @strSql = 'Select top' +str(@PageSize)+ ' ' + @tbFields + ' from '+ @tbName +' where ' + @strWhere + ' ' + @strOrder
end
else
begin
set @strSql = 'Select top' +str(@PageSize)+ ' ' + @tbFields + ' from '+ @tbName + ' ' + @strOrder
end
end
--------------分页不为第一页------------
else
begin
if @strWhere !=''
begin
set @strSql='Select top ' +str(@PageSize) + ' ' + @tbFields
+' from ' + @tbName + ' where '+@OrderField +' '
+ @strtemp+ ' ' +@OrderField+ ') from (select top '
+str(@PageIndex*@PageSize) + ' '+@OrderField +' from '+
@tbName+' where '+@strwhere + ' '+ @strOrder +') as tb) and '+@strwhere+' '+ @strOrder
end
else
begin
set @strSql='Select top ' + str(@PageSize) + ' ' + @tbFields
+' from ' + @tbName + ' where '+@OrderField + ' '
+ @strtemp+ ' ' +@OrderField+ ') from (select top'
+str(@PageIndex*@PageSize) + ' '+@OrderField +' from '+@tbName
+ ' '+ @strOrder +') as tb)'+ @strOrder
end
end
exec (@strSql)
end else
begin
print 'wrong page'
end大家觉得这种分页怎样?我觉得挺臃肿的(语句)上.大家有什么好的分页方法SQL ?
exec SqlDataPaging 'table_name','*','id','10','0','1','id>0 ;drop table xxx--',null
改两个地方:
--------------------------------------------------
set @strSqlCount='Select @TotalCout=count(*) from ' + @tbName + ' where '+ @strWhere----->>> set @strSqlCount='Select @TotalCout=count(*) from (select * from ' + @tbName + ' where '+ @strWhere + ') as Tmp'
--------------------------------------------------
if @strWhere !=''
begin
set @strSql = 'Select top' +str(@PageSize)+ ' ' + @tbFields + ' from '+ @tbName +' where ' + @strWhere + ' ' + @strOrder
end
else
begin
set @strSql = 'Select top' +str(@PageSize)+ ' ' + @tbFields + ' from '+ @tbName + ' ' + @strOrder
end
----->>>
if @strWhere !=''
begin
set @strSql = 'select * from (Select top' +str(@PageSize)+ ' ' + @tbFields + ' from '+ @tbName +' where ' + @strWhere + ' ' + @strOrder + ') as T ' + @strOrder
end
else
begin
set @strSql = 'select * from (Select top' +str(@PageSize)+ ' ' + @tbFields + ' from '+ @tbName + ') as T ' + @strOrder
end
-------------------------------------------------