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