sql 存储过程分页

　　CREATEPROCmyx_prPageRecordset
@queryStrnvarchar(1000),
@keyFieldnvarchar(200),
@pageSizeint,
@pageNumberint
AS
BEGIN
DECLARE@sqlTextASnvarchar(4000)
DECLARE@sqlTableASnvarchar(4000)
SET@sqlTable=SELECTTOP+CAST((@pageNumber+1)*@pageSizeASvarchar(30))++@queryStr
SET@sqlText=
SELECTTOP+CAST(@pageSizeASvarchar(30))+*+
FROM(+@sqlTable+)AStableA+
WHERE+@keyField+NOTIN(SELECTTOP+
CAST(@pageNumber*@pageSizeASvarchar(30))++@keyField+
FROM(+@sqlTable+)AStableB)
EXEC(@sqlText)
END

GO

核心代码
DimstrsqlAsString
myComm=NewSqlClient.SqlCommand(myx_prPageRecordset,myConn)
myComm.Parameters.Add(NewSqlClient.SqlParameter(@queryStr,SqlDbType.NVarChar,1000))
myComm.Parameters(@queryStr).Value=*fromtbpageorderbyidDESC
myComm.Parameters.Add(NewSqlClient.SqlParameter(@keyField,SqlDbType.NVarChar,200))
myComm.Parameters(@keyField).Value=[id]
myComm.Parameters.Add(NewSqlClient.SqlParameter(@pageSize,SqlDbType.NVarChar,1000))
myComm.Parameters(@pageSize).Value=PageSize
myComm.Parameters.Add(NewSqlClient.SqlParameter(@pageNumber,SqlDbType.NVarChar,1000))
myComm.Parameters(@pageNumber).Value=myPage-1

呵呵,执行几W条的代码只需150毫秒左右

建立一个test(id,name,fid)
向test添充几十条数据,使id=1,2,3,4.........(即递增的integer),其他任意
在T-sqlDebugger给改存储过程分别传递如下参数:
@queryStr=*fromtest
@keyField=[ID]
@pageSize=3
@pageNumber=1
问题出来了,看输出结果(注意id):
idnamefid
4kwklover2
5kwklover2
6kwklover2
根据传入参数,我们的预期应该是:
idnamefid
1kwklover2
2kwklover2
3kwklover2

下面是我参照小春的存储分页写的分页存储过程,可以解决上面的问题:
CREATEProcedureprGetRecordByPage
(
@PageSizeint,--每页的记录条数
@PageNumberint,--当前页面
@QuerySqlvarchar(1000),--部分查询字符串,如*FromTestorderbyiddesc
@KeyFieldvarchar(500)
)
AS
Begin

Declare@SqlTableASvarchar(1000)
Declare@SqlTextASVarchar(1000)

Set@SqlTable=SelectTop+CAST(@PageNumber*@PageSizeASvarchar(30))++@QuerySql
Set@SqlText=SelectTop+Cast(@PageSizeASvarchar(30))+*From
+(+@SqlTable+)AsTembTbA
+Where+@KeyField+NotIn(SelectTop+CAST((@PageNumber-1)*@PageSizeASvarchar(30))++@KeyField+From
+(+@SqlTable+)ASTempTbB)
Exec(@SqlText)

End
GO