简单sqlserver 分页存储过程创建 及调用

--数据准备

create table sc
(
    s# int,
    c# varchar(3),
    score int
)
insert into sc(s#,c#,score)
select 1,'001',70 union all
select 1,'002',80 union all
select 1,'003',59 union all
select 2,'001',50 union all
select 2,'002',70 union all
select 2,'003',90 union all
select 3,'001',70 union all
select 3,'002',50 union all
select 3,'003',70 union all
select 3,'004',55 union all
select 3,'005',66 union all
select 2,'004',34 union all
select 4,'005',50 union all
select 4,'004',21

--创建存储过程
create procedure pro_sc_paging
     @pagesize int, 
     @pageindex int
as
begin
    select * from 
    (select sc.*, row_number() over(order by sc.c#,sc.s#) rn
    from sc) t
    where t.rn >= (@pageindex-1) * @pagesize + 1 and t.rn <= @pagesize* @pageindex
end

--调用
exec pro_sc_paging 2,4

转载于:https://my.oschina.net/dyc1122/blog/1518573