分页存储过程分页不对.第一页为1~5记录(5条),第二页是5~10记录(6条)

[fgmis]

Sql2005分页存储过程.定义为5条记录为一页.
第一页(5条)
1
2
3
4
5
第二页(6条)
5
6
7
8
9
10
怎么会这样的,请问错误在哪里?

[fgmis]

BEGIN
SET NOCOUNT ON;

Declare @Sql nVarchar(1000), @Sql2 NVARCHAR(500)

Set @Sql=
'Select '+@strGetFields+'
From (Select '+@strGetFields+',ROW_NUMBER() OVER(ORDER BY '+@keyID+') AS ROWNUMBER
From '+@tblName+
Case IsNull(@strWhere,'') When '' Then '' Else ' Where '+@strWhere End+')T
Where ROWNUMBER Between '+Cast(@pageSize*(@pageIndex-1) As Varchar(10))+' And '+Cast(@pageSize*@pageIndex As Varchar(10))+
Case IsNull(' Order By '+@strOrderBy,'') When '' Then '' Else
' Order By '+@strOrderBy End

Exec(@Sql)

Set @Sql2='Select 1 As Count From '+@tblName+Case IsNull(@strWhere,'') When
'' Then '' Else ' Where '+@strWhere End
Exec(@Sql2)

SET @totalCount = @@ROWCOUNT

--SET @result = 0
END

[marco08]

學習

[撸大湿]

BEGIN
SET NOCOUNT ON;

Declare @Sql nVarchar(1000), @Sql2 NVARCHAR(500)

Set @Sql=
'Select '+@strGetFields+'
From (Select '+@strGetFields+',ROW_NUMBER() OVER(ORDER BY '+@keyID+') AS ROWNUMBER
From '+@tblName+
Case IsNull(@strWhere,'') When '' Then '' Else ' Where '+@strWhere End+')T
Where ROWNUMBER Between '+
--LOOK
Cast(@pageSize*(@pageIndex-1)+1 As Varchar(10)) --LOOK
--LOOK
+' And '+Cast(@pageSize*@pageIndex As Varchar(10))+
Case IsNull(' Order By '+@strOrderBy,'') When '' Then '' Else
' Order By '+@strOrderBy End

Exec(@Sql)

Set @Sql2='Select 1 As Count From '+@tblName+Case IsNull(@strWhere,'') When
'' Then '' Else ' Where '+@strWhere End
Exec(@Sql2)

SET @totalCount = @@ROWCOUNT

--SET @result = 0
END