一个通用的分页存储过程，当pagerIndex = 2的时候就出错。请大家帮忙看一下是哪出问题了！

[云中雁荡山]

--参数说明-------------------------------------------------------------
/**//*
@strTable --要显示的表或多个表的连接
@strField --要查询出的字段列表,*表示全部字段
@intTop --最多读取记录数
@pageSize --每页显示的记录个数
@pageIndex --要显示那一页的记录
@strWhere --查询条件,不需where
@strSortKey --用于排序的主键
@strSortField --用于排序，如：id desc (多个id desc,dt asc)
@strOrderBy --排序,0-顺序,1-倒序
@pageCount --查询结果分页后的总页数
@RecordCount --查询到的总记录数
@UsedTime --耗时测试时间差
*/
ALTER PROCEDURE [dbo].[ThePagerIndex]
@strTable varchar(1000) = '[dbo].[ttable]',
@strField varchar(1000) = '*',
@intTop int = 5000,
@pageSize int = 20,
@pageIndex int = 1,
@strWhere varchar(1000) = '1=1',
@strSortKey varchar(1000) = 'id',
@strSortField varchar(500) = 'id DESC',
@strOrderBy bit = 1,
@pageCount int OUTPUT,
@RecordCount int OUTPUT
--@UsedTime int OUTPUT
AS
SET NOCOUNT ON
Declare @sqlcount INT
Declare @timediff DATETIME
select @timediff=getdate()
Begin Tran
DECLARE @sql nvarchar(max),@where1 varchar(max),@where2 varchar(max)
	IF @strWhere is null or rtrim(@strWhere)=''
		BEGIN--没有查询条件
			SET @where1=' WHERE '
			SET @where2=' '
		END
	ELSE
		BEGIN--有查询条件
			SET @where1=' WHERE ('+@strWhere+') AND ' --本来有条件再加上此条件
			SET @where2=' WHERE ('+@strWhere+') ' --原本没有条件而加上此条件
		END
	--SET @sql='SELECT @intResult=COUNT(*) FROM '+@strTable+@where2
	IF @intTop<=0
		BEGIN
			SET @sql='SELECT @sqlcount=COUNT(*) FROM (select '+@strSortKey+' from '+ @strTable + @where2 +') As tmptab'
		END
	ELSE
		BEGIN
			SET @sql='SELECT @sqlcount=COUNT(*) FROM (select top '+ cast(@intTop as varchar(max)) +' '+@strSortKey+' from '+ @strTable + @where2 +') As tmptab'
		END
	--print @sql

EXEC sp_executesql @sql,N'@sqlcount int OUTPUT',@sqlcount OUTPUT --计算总记录数
SELECT @pageCount=CEILING((@sqlcount+0.0)/@pageSize) --计算总页数
SELECT @RecordCount = @sqlcount --设置总记录数
	IF @pageIndex=1 --第一页
		BEGIN
			SET @sql='SELECT TOP '+CAST(@pageSize AS varchar(max))+' '+@strField+' FROM '+@strTable+@where2+'ORDER BY '+ @strSortField
		END
	Else
		BEGIN
			IF @strOrderBy=0
				SET @sql='SELECT TOP '+CAST(@pageSize AS varchar(max))+' '+@strField+ ' FROM '+
					@strTable+@where1+@strSortKey+'>(SELECT MAX('+@strSortKey+') '+ ' FROM (SELECT TOP '+
					CAST(@pageSize*(@pageIndex-1) AS varchar(max))+' '+@strSortKey+' FROM '+@strTable+@where2+
					'ORDER BY '+@strSortField+') t) ORDER BY '+@strSortField
			ELSE
				SET @sql='SELECT TOP '+CAST(@pageSize AS varchar(max))+' '+@strField+' FROM '+@strTable+@where1+
					@strSortKey+'<(SELECT MIN('+@strSortKey+') '+ ' FROM (SELECT TOP '+CAST(@pageSize*(@pageIndex-1) AS varchar(max))+' '+
					@strSortKey+' FROM '+@strTable+@where2+'ORDER BY '+@strSortField+') t) ORDER BY '+@strSortField+''
		END
print @sql
EXEC(@sql)
print @sql
If @@Error <> 0
Begin
RollBack Tran
Return -1
End
Else
Begin
Commit TRAN
--set @UsedTime = datediff(ms,@timediff,getdate())
--select datediff(ms,@timediff,getdate()) as 耗时
Return @sqlcount
End

[SQLCenter]

将出错信息也贴出来吧

[pt1314917]

什么错误呢？

[SQLCenter]

 IF @strOrderBy=0
                SET @sql='SELECT TOP '+CAST(@pageSize AS varchar(max))+' '+@strField+ ' FROM '+
                    @strTable+@where1+@strSortKey+'>(SELECT MAX('+@strSortKey+') '+ ' FROM (SELECT TOP '+
                    CAST(@pageSize*(@pageIndex-1) AS varchar(max))+' '+@strSortKey+' FROM '+@strTable+@where2+
                    'ORDER BY '+@strSortField+') t) ORDER BY '+@strSortField
            ELSE
                SET @sql='SELECT TOP '+CAST(@pageSize AS varchar(max))+' '+@strField+' FROM '+@strTable+@where1+
                    @strSortKey+'<(SELECT MIN('+@strSortKey+') '+ ' FROM (SELECT TOP '+CAST(@pageSize*(@pageIndex-1) AS varchar(max))+' '+
                    @strSortKey+' FROM '+@strTable+@where2+'ORDER BY '+@strSortField+') t) ORDER BY '+@strSortField+''

这样是不对的：

@strSortKey --用于排序的主键
@strSortField --用于排序，如：id desc (多个id desc,dt asc)
@strOrderBy --排序,0-顺序,1-倒序

@strSortKey = @strSortField 时你怎么确定min()/max() ？

[云中雁荡山]

错误信息如下：

SELECT TOP 10 * FROM CardInfo left join CardTypeInfo on CardInfo.CardTypeID = CardTypeInfo.CardTypeID WHERE (CardInfo.CardSellStatus = 1) AND CardInfo.CardID>(SELECT MAX(CardInfo.CardID)  FROM (SELECT TOP 20 CardInfo.CardID FROM CardInfo left join CardTypeInfo on CardInfo.CardTypeID = CardTypeInfo.CardTypeID WHERE (CardInfo.CardSellStatus = 1) ORDER BY CardInfo.CardID asc) t) ORDER BY CardInfo.CardID asc
消息 147，级别 15，状态 1，第 1 行
聚合不应出现在 WHERE 子句中，除非该聚合位于 HAVING 子句或选择列表所包含的子查询中，并且要对其进行聚合的列是外部引用。
SELECT TOP 10 * FROM CardInfo left join CardTypeInfo on CardInfo.CardTypeID = CardTypeInfo.CardTypeID WHERE (CardInfo.CardSellStatus = 1) AND CardInfo.CardID>(SELECT MAX(CardInfo.CardID)  FROM (SELECT TOP 20 CardInfo.CardID FROM CardInfo left join CardTypeInfo on CardInfo.CardTypeID = CardTypeInfo.CardTypeID WHERE (CardInfo.CardSellStatus = 1) ORDER BY CardInfo.CardID asc) t) ORDER BY CardInfo.CardID asc

(1 行受影响)

(1 行受影响)

[云中雁荡山]

3楼的朋友能否指出如何修改，分不够再加。多谢！

[SQLCenter]

不是分不分的问题

我看你里面有varchar(max)，应该2005或以上，为什么不用row_number()分页？

[SQLCenter]

2000的通用分页思路，一般是这样的：LEFT JOIN

比你的MIN/MAX好控制很多

declare @pageSize int, @pageIndex int
select @pageSize = 5, @pageIndex = 2

select
	top (@pageSize) a.*
from
	sysobjects a
left join
	(select top ((@pageIndex-1)*@pageSize) id from sysobjects) b
	on a.id = b.id
where
	b.id is null

[等不到来世]

这个所谓的通用存储过程根本没有考虑表别名的问题。
小改了一下，试试：

--参数说明-------------------------------------------------------------
/**//*
@strTable --要显示的表或多个表的连接
@strField --要查询出的字段列表,*表示全部字段
@intTop --最多读取记录数
@pageSize --每页显示的记录个数
@pageIndex --要显示那一页的记录
@strWhere --查询条件,不需where
@strSortKey --用于排序的主键
@strSortField --用于排序，如：id desc (多个id desc,dt asc)
@strOrderBy --排序,0-顺序,1-倒序
@pageCount --查询结果分页后的总页数
@RecordCount --查询到的总记录数
@UsedTime --耗时测试时间差
*/
ALTER PROCEDURE [dbo].[ThePagerIndex]
@strTable varchar(1000) = '[dbo].[ttable]',
@strField varchar(1000) = '*',
@intTop int = 5000,
@pageSize int = 20,
@pageIndex int = 1,
@strWhere varchar(1000) = '1=1',
@strSortKey varchar(1000) = 'id',
@strSortField varchar(500) = 'id DESC',
@strOrderBy bit = 1,
@pageCount int OUTPUT,
@RecordCount int OUTPUT
--@UsedTime int OUTPUT
AS
SET NOCOUNT ON
Declare @sqlcount INT
Declare @timediff DATETIME
select @timediff=getdate()
Begin Tran
DECLARE @sql nvarchar(max),@where1 varchar(max),@where2 varchar(max)
    IF @strWhere is null or rtrim(@strWhere)=''
        BEGIN--没有查询条件
            SET @where1=' WHERE '
            SET @where2=' '
        END
    ELSE
        BEGIN--有查询条件
            SET @where1=' WHERE ('+@strWhere+') AND ' --本来有条件再加上此条件
            SET @where2=' WHERE ('+@strWhere+') ' --原本没有条件而加上此条件
        END
    --SET @sql='SELECT @intResult=COUNT(*) FROM '+@strTable+@where2
    IF @intTop<=0
        BEGIN
            SET @sql='SELECT @sqlcount=COUNT(*) FROM (select '+@strSortKey+' from '+ @strTable + @where2 +') As tmptab'
        END
    ELSE
        BEGIN
            SET @sql='SELECT @sqlcount=COUNT(*) FROM (select top '+ cast(@intTop as varchar(max)) +' '+@strSortKey+' from '+ @strTable + @where2 +') As tmptab'
        END
    --print @sql

EXEC sp_executesql @sql,N'@sqlcount int OUTPUT',@sqlcount OUTPUT --计算总记录数
SELECT @pageCount=CEILING((@sqlcount+0.0)/@pageSize) --计算总页数
SELECT @RecordCount = @sqlcount --设置总记录数
    IF @pageIndex=1 --第一页
        BEGIN
            SET @sql='SELECT TOP '+CAST(@pageSize AS varchar(max))+' '+@strField+' FROM '+@strTable+@where2+'ORDER BY '+ @strSortField
        END
    Else
        BEGIN
            IF @strOrderBy=0
                SET @sql='SELECT TOP '+CAST(@pageSize AS varchar(max))+' '+@strField+ ' FROM '+
                    @strTable+@where1+@strSortKey+'>(SELECT MAX('+case when charindex('.',@strSortKey)>0 then right(@strSortKey,len(@strSortKey)-charindex('.',@strSortKey)) else @strSortKey end+') '+ ' FROM (SELECT TOP '+
                    CAST(@pageSize*(@pageIndex-1) AS varchar(max))+' '+@strSortKey+' FROM '+@strTable+@where2+
                    'ORDER BY '+@strSortField+') t) ORDER BY '+@strSortField
            ELSE
                SET @sql='SELECT TOP '+CAST(@pageSize AS varchar(max))+' '+@strField+' FROM '+@strTable+@where1+
                    @strSortKey+'<(SELECT MIN('+case when charindex('.',@strSortKey)>0 then right(@strSortKey,len(@strSortKey)-charindex('.',@strSortKey)) else @strSortKey end+') '+ ' FROM (SELECT TOP '+CAST(@pageSize*(@pageIndex-1) AS varchar(max))+' '+
                    @strSortKey+' FROM '+@strTable+@where2+'ORDER BY '+@strSortField+') t) ORDER BY '+@strSortField+''
        END
print @sql
EXEC(@sql)
print @sql
If @@Error <> 0
Begin
RollBack Tran
Return -1
End
Else
Begin
Commit TRAN
--set @UsedTime = datediff(ms,@timediff,getdate())
--select datediff(ms,@timediff,getdate()) as 耗时
Return @sqlcount
End