SQL 通用分页 高手请帮忙修正
CREATE PROCEDURE pagination3
@tblName varchar(255), -- 表名
@strGetFields varchar(1000) = ' '* ' ', -- 需要返回的列
@fldName varchar(255)= ' ' ' ', -- 排序的字段名
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@doCount bit = 0, -- 返回记录总数, 非 0 值则返回
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(1500) = ' ' ' ' -- 查询条件 (注意: 不要加 where)
AS
declare @strSQL varchar(5000) -- 主语句
declare @strTmp varchar(110) -- 临时变量
declare @strOrder varchar(400) -- 排序类型
if @doCount != 0
begin
if @strWhere != ' ' ' '
set @strSQL = "select count(*) as Total from [ " + @tblName + "] where "+@strWhere
else
set @strSQL = "select count(*) as Total from [ " + @tblName + "] "
end 以上代码的意思是如果@doCount传递过来的不是0，就执行总数统计。以下的所有代码都是@doCount为0的情况：
else
begin
if @OrderType != 0
begin
set @strTmp = " <(select min "
set @strOrder = " order by [ " + @fldName + "] desc "如果@OrderType不是0，就执行降序，这句很重要！
end
else
begin
set @strTmp = "> (select max "
set @strOrder = " order by [ " + @fldName + "] asc "
end
if @PageIndex = 1
begin
if @strWhere != ' ' ' '
set @strSQL = "select top " + str(@PageSize) + " "+@strGetFields+ "
from [ " + @tblName + "] where " + @strWhere + " " + @strOrder
else
set @strSQL = "select top " + str(@PageSize) + " "+@strGetFields+ "
from [ "+ @tblName + "] "+ @strOrder如果是第一页就执行以上代码，这样会加快执行速度
end
else
begin以下代码赋予了@strSQL以真正执行的SQL代码
set @strSQL = "select top " + str(@PageSize) + " "+@strGetFields+ " from [ "
+ @tblName + "] where [ " + @fldName + "] " + @strTmp + "([ "+ @fldName + "])
from (select top " + str((@PageIndex-1)*@PageSize) + " [ "+ @fldName + "]
from [ " + @tblName + "] " + @strOrder + ") as tblTmp) "+ @strOrder
if @strWhere != ' ' ' '
set @strSQL = "select top " + str(@PageSize) + " "+@strGetFields+ " from [ "
+ @tblName + "] where [ " + @fldName + "] " + @strTmp + "([ "
+ @fldName + "]) from (select top " + str((@PageIndex-1)*@PageSize) + " [ "
+ @fldName + "] from [ " + @tblName + "] where " + @strWhere + " "
+ @strOrder + ") as tblTmp) and " + @strWhere + " " + @strOrder
end
end
exec (@strSQL)
GO
引用
http://sh10.cn/bbs/dispbbs.asp?boardID=12&ID=20071&page=1
[解决办法]
mssql 2005的通用存储过程：
create procedure [dbo].[Pagination](
@pagesize int,-- 页大小
@pageindex int,-- 当前页
@identity varchar(100),-- 唯一列
@cells varchar(1000)= '* ',-- 显示列
@tables varchar(1000),-- 表名称
@condition varchar(2000)=NULL,-- 查询条件
@top int = -1,--
@orderby varchar(200)=NULL-- 排序 desc
)
as
set nocount on
declare @rowcount int;
declare @SQL nvarchar(4000);
set @SQL = N 'select @rowcount = count(0) from ' + @tables + ' where 1=1 ';
if(@condition IS NOT NULL) set @SQL = @SQL + @condition;
exec sp_executesql @SQL,N '@rowcount int output ',@rowcount output;
if(@top != -1 and @rowcount > @top) set @rowcount = @top;
set @SQL = N 'declare @___lb int; ';
set @SQL = @SQL + N 'declare @___ub int; ';
set @SQL = @SQL + N 'set @___lb=(@pageindex-1)*@pagesize; ';
set @SQL = @SQL + N 'set @___ub=@___lb+@pagesize; ';
set @SQL = @SQL + N 'set ROWCOUNT @___ub; ';
set @SQL = @SQL + N 'with ___t as (select * ';
if(@orderby is not null and @orderby != ' ')
set @SQL = @SQL + N ',row_number() over(order by ' + @orderby + ', ' + @identity + ' asc) as ___p from ( ';
else
set @SQL = @SQL + N ',row_number() over(order by ' + @identity + ' asc) as ___p from ( ';
if(@top != -1)
set @SQL = @SQL + N 'select top ' + cast(@top as nvarchar(10)) + ' ';
else
set @SQL = @SQL + N 'select ';
if(@cells IS NOT NULL)
set @SQL = @SQL + @cells;
else
set @SQL = @SQL + N '* ';
set @SQL = @SQL + ' from ' + @tables + ' where 1=1 ';
if(@condition IS NOT NULL and @condition != ' ') set @SQL = @SQL + ' ' + @condition;
set @SQL = @SQL + ') as ___q) select * from ___t where ___p> @___lb and ___p <=@___ub; ';
exec sp_executesql @SQL,N '@pagesize int,@pageindex int ',@pagesize = @pagesize,@pageindex = @pageindex;
return @rowcount;
set nocount off
