用存储过程分页怎么写
所有回答(4)
0
开头 select top pageIndex*pageLength ..... where id not in (select top (pageIndex-1)*pageLength)
意思：取出前50条，然后id不属于前40条的 - 就是 41-50条了
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 '查询语句', 当前页码,分页数量,总页数"
调用的时候执行这条语句就可以了！
谢谢
0
http://www.webdiyer.com/Controls/AspNetPager/SpGenerator
自己去这个地方找吧
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
