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.CommandType=CommandType.StoredProcedure
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