如何在SQL Server中的存储过程中写分页 [英] how to write a paging in stored procesure in sql server

问题描述

我要实现分页，我要编写一个数据过程，在该过程中我要实现分页.如果您知道如何在sqlserver的存储过程中实现分页.
在此先感谢.

i want to implement the paging, i write a procedure for data,in that procedure i want implement the paging.if You know how to implement the paging in stored procedure in sqlserver.
Thanks in Advance.

推荐答案

CREATE PROCEDURE [dbo].[asp_Pagingtest]
( @index as int     =1  )

Begin

--something
--something

--something

 Declare @StartIndex as int=((@index-1)*10)+1
Declare @EndIndex as Int=@index*10


With CTE_Count as
(Select EmpId,ROW_NUMBER() OVER (ORDER BY  S.LastName) AS RowNumber
 from table_temp )
Select * from table_temp where EmpId in (select EmpId from CTE_COunt where rownumber between @StartIndex  and @EndIndex)

END

这里@Index默认为pageno，我们必须显示第一页. EmpId是我要在其中提取记录的表的主键

Here @Index is the pageno by default, we have to dispaly first page. EmpId is the primary key for the table where i am going to pull records

以及一些不错的文章:
-使用ROW_NUMBER()对SQL Server进行数据分页2005和ASP.NET [ SQL Server 2005分页结果 [

And few good articles:
- Using ROW_NUMBER() to paginate your data with SQL Server 2005 and ASP.NET[^]
- SQL Server 2005 Paging Results[^]

放入存储过程中的此查询

Put this query in store procedure

select * from
(
select row_number over(partition by id,name order by id,name) as index_no,--this will assign row no 1,2,3,...
id,name
from tbl 
) as a
where index_no>@StartIndex --pass here from-to record-no range.
      and index_no<@EndIndex

传递参数``@StartIndex''&单击按钮时的"@EndIndex"范围.
例如. 1-10、11-20，...
祝您编码愉快！
:)

pass parameters ''@StartIndex'' & ''@EndIndex'' range while clicking button.
eg. 1-10, 11-20,...
Happy Coding!
:)

这篇关于如何在SQL Server中的存储过程中写分页的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！