首页 新闻 搜索 专区 学院

用存储过程分页怎么写

0
[待解决问题]

不会用存储过程分页,谁能告诉一下,谢谢

杀  手的主页 杀 手 | 菜鸟二级 | 园豆:213
提问于:2011-05-17 12:28
< >
分享
所有回答(4)
0

开头 select top pageIndex*pageLength   .....   where id  not in (select top (pageIndex-1)*pageLength)

意思:取出前50条,然后id不属于前40条的 - 就是 41-50条了

失落小羊 | 园豆:58 (初学一级) | 2011-05-17 12:46
0
View Code
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[USP_SplitPage]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[USP_SplitPage]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


--通用分页存储过程
CREATE PROCEDURE USP_SplitPage
@sql Nvarchar(4000), --要执行的sql语句
@currentpage INT = 1, --要显示的页码
@pagesize INT = 10, --每页的大小
@pagecount INT = 0 OUT --总页数
AS

SET NOCOUNT ON

DECLARE
@p1 INT

EXEC sp_cursoropen @p1 OUTPUT,@sql,@scrollopt=1,@ccopt=1,@rowcount=@pagecount OUTPUT

SET @pagecount=CEILING(1.0*@pagecount/@pagesize)
SET @currentpage=(@currentpage-1)*@pagesize+1

EXEC sp_cursorfetch @p1,16,@currentpage,@pagesize
EXEC sp_cursorclose @p1

--SELECT @currentpage
SELECT @pagecount

SET NOCOUNT OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

 

strSql="USP_SplitPage '查询语句', 当前页码,分页数量,总页数"

调用的时候执行这条语句就可以了!

/aiq浪子飞龙 | 园豆:1189 (小虾三级) | 2011-05-17 13:56
谢谢
支持(0) 反对(0) 杀 手 | 园豆:213 (菜鸟二级) | 2011-05-17 14:08
0

http://www.webdiyer.com/Controls/AspNetPager/SpGenerator

自己去这个地方找吧

顾晓北 | 园豆:10792 (专家六级) | 2011-05-18 09:13
0

--通过开始行和结束行的索引获取结果集的存储过程--
create procedure sp_Paging_GetStu

@startIndex int,

@endIndex int

as

with table_temp as (select row_number() over(order by  stuid) as rowIndex,* from Stu)

select * from table_temp where rowIndex between @startIndex and @endIndex

go

--通过开始行和结束行的索引获取结果集的函数--

create function ft_Paging_GetStu(@startIndex int,@endIndex int)

returns table

as

return(with table_temp as (select row_number() over(order by stuid) as rowIndex,* from stu)

select * from table_temp where rowIndex between @startIndex and @endIndex)

go

剪、爱 | 园豆:211 (菜鸟二级) | 2011-05-26 11:21
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册