所属类别：ASP编程

特别推荐：免费发布信息 承包关键词~~抢爆了！^HOT!

CREATE PROC myx_prPageRecordset@queryStr nvarchar(1000),@keyField nvarchar (200),@pageSize int,@pageNumber intASBEGINDECLARE @sqlText AS nvarchar(4000)DECLARE @sqlTable AS nvarchar(4000)SET @sqlTable = 'SELECT TOP ' + CAST((@pageNumber + 1) * @pageSize AS varchar(30)) + ' ' + @queryStrSET @sqlText ='SELECT TOP ' + CAST(@pageSize AS varchar(30)) + ' * ' +'FROM (' + @sqlTable + ') AS tableA ' +'WHERE ' + @keyField + ' NOT IN(SELECT TOP ' +CAST(@pageNumber * @pageSize AS varchar(30)) + ' ' + @keyField +' FROM (' + @sqlTable + ') AS tableB)'EXEC (@sqlText)ENDGO核心代码Dim strsql As StringmyComm = New SqlClient.SqlCommand("myx_prPageRecordset", myConn)myComm.CommandType = CommandType.StoredProceduremyComm.Parameters.Add(New SqlClient.SqlParameter("@queryStr", SqlDbType.NVarChar, 1000))myComm.Parameters("@queryStr").Value = " * from tbpage order by id DESC"myComm.Parameters.Add(New SqlClient.SqlParameter("@keyField", SqlDbType.NVarChar, 200))myComm.Parameters("@keyField").Value = "[id]"myComm.Parameters.Add(New SqlClient.SqlParameter("@pageSize", SqlDbType.NVarChar, 1000))myComm.Parameters("@pageSize").Value = PageSizemyComm.Parameters.Add(New SqlClient.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-sql Debugger给改存储过程分别传递如下参数:@queryStr= * from test@keyField=[ID]@pageSize=3@pageNumber=1问题出来了,看输出结果(注意id):id name fid4 kwklover 25 kwklover 26 kwklover 2根据传入参数,我们的预期应该是:id name fid1 kwklover 22 kwklover 23 kwklover 2下面是我参照小春的存储分页写的分页存储过程,可以解决上面的问题:CREATE Procedure prGetRecordByPage(@PageSize int, --每页的记录条数@PageNumber int, --当前页面@QuerySql varchar(1000),--部分查询字符串,如* From Test order by id desc@KeyField varchar(500))ASBeginDeclare @SqlTable AS varchar(1000)Declare @SqlText AS Varchar(1000)Set @SqlTable='Select Top '+CAST(@PageNumber*@PageSize AS varchar(30))+' '+@QuerySqlSet @SqlText='Select Top '+Cast(@PageSize AS varchar(30))+' * From '+'('+@SqlTable+') As TembTbA '+'Where '+@KeyField+' Not In (Select Top '+CAST((@PageNumber-1)*@PageSize AS varchar(30))+' '+@KeyField+' From '+'('+@SqlTable+') AS TempTbB)'Exec(@SqlText)EndGO

....