sql 存储过程分页
网络编程 2021-07-05 10:58www.168986.cn编程入门
CREATE PROC myx_prPageRecordset
@queryStr nvarchar(1000),
@keyField nvarchar (200),
@pageSize int,
@pageNumber int
AS
BEGIN
DECLARE @sqlText AS nvarchar(4000)
DECLARE @sqlTable AS nvarchar(4000)
SET @sqlTable = 'SELECT TOP ' + CAST((@pageNumber + 1) * @pageSize AS varchar(30)) + ' ' + @queryStr
SET @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)
END
GO
核心代码
Dim strsql As String
myComm = New SqlClient.SqlCommand("myx_prPageRecordset", myConn)
myComm.CommandType = CommandType.StoredProcedure
myComm.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 = PageSize
myComm.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 fid
4 kwklover 2
5 kwklover 2
6 kwklover 2
根据传入参数,我们的预期应该是:
id name fid
1 kwklover 2
2 kwklover 2
3 kwklover 2
狼蚁网站SEO优化是我参照小春的存储分页写的分页存储过程,可以解决上面的问题:
CREATE Procedure prGetRecordByPage
(
@PageSize int, --每页的记录条数
@PageNumber int, --当前页面
@QuerySql varchar(1000),--部分查询字符串,如* From Test order by id desc
@KeyField varchar(500)
)
AS
Begin
Declare @SqlTable AS varchar(1000)
Declare @SqlText AS Varchar(1000)
Set @SqlTable='Select Top '+CAST(@PageNumber*@PageSize AS varchar(30))+' '+@QuerySql
Set @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)
End
GO
@queryStr nvarchar(1000),
@keyField nvarchar (200),
@pageSize int,
@pageNumber int
AS
BEGIN
DECLARE @sqlText AS nvarchar(4000)
DECLARE @sqlTable AS nvarchar(4000)
SET @sqlTable = 'SELECT TOP ' + CAST((@pageNumber + 1) * @pageSize AS varchar(30)) + ' ' + @queryStr
SET @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)
END
GO
核心代码
Dim strsql As String
myComm = New SqlClient.SqlCommand("myx_prPageRecordset", myConn)
myComm.CommandType = CommandType.StoredProcedure
myComm.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 = PageSize
myComm.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 fid
4 kwklover 2
5 kwklover 2
6 kwklover 2
根据传入参数,我们的预期应该是:
id name fid
1 kwklover 2
2 kwklover 2
3 kwklover 2
狼蚁网站SEO优化是我参照小春的存储分页写的分页存储过程,可以解决上面的问题:
CREATE Procedure prGetRecordByPage
(
@PageSize int, --每页的记录条数
@PageNumber int, --当前页面
@QuerySql varchar(1000),--部分查询字符串,如* From Test order by id desc
@KeyField varchar(500)
)
AS
Begin
Declare @SqlTable AS varchar(1000)
Declare @SqlText AS Varchar(1000)
Set @SqlTable='Select Top '+CAST(@PageNumber*@PageSize AS varchar(30))+' '+@QuerySql
Set @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)
End
GO
上一篇:邹建的分页存储过程改了一下
下一篇:SQL Server 存储过程的分页
编程语言
- 机械手焊接机器人编程 机械手焊接设备编程
- vb数据库编程实例,vb做数据库
- tiobe世界编程语言排行榜,全球编程语言排行
- 成都java编程培训 成都it编程培训中心
- 编程基础培训,编程基础知识培训
- flash游戏编程基础教程,flash游戏开发教程
- 不会英语能学编程吗 不会英语可以学程序员吗
- 爱心代码编程C语言 爱心代码编程c语言公式
- c语言编程学习入门 c语言编程快速入门
- 西门子plc编程培训 西门子plc编程培训班
- 哪里可以学编程 台州哪里可以学编程
- 少儿编程哪个好 少儿编程哪个品牌好
- 编程是什么意思,图形编程是什么意思
- c语言编程软件下载,c语言编程软件在哪下载
- java编程软件下载,java编程下载手机版
- ug编程培训学校,ug编程培训教程