sql server 存储过程分页

Question

create proc usp_test4
@PageSize int = 10,
@PageIndex int = 1
as
begin
declare @PageCount int
select @PageCount = COUNT(*) from TestDB
select @PageCount PageCount
select * from
(
select ROW_NUMBER() over(order by id desc) as AID ,id as ID,Name as '姓名',Age as '年龄',
(
case
when Sex='m' then '男'
when Sex='f' then '女'
end
) as '性别' from TestDB
) as t1
where t1.AID between @PageSize*(@PageIndex-1)+1 and @PageSize*@PageIndex
end

exec usp_test4 5,1

这样可以查询出分页后的信息和总条数，这样写 在数据层可以查询出来吗 。

Answer 1

create proc usp_test2
@PageSize int = 10,
@PageIndex int = 1,
@PageCount int output
as
begin
select @PageCount = COUNT(*) from TestDB
select * from
(
select ROW_NUMBER() over(order by id desc) as AID ,id as ID,Name as '姓名',Age as '年龄',
(
case
when Sex='m' then '男'
when Sex='f' then '女'
end
) as '性别' from TestDB
) as t1
where t1.AID between @PageSize*(@PageIndex-1)+1 and @PageSize*@PageIndex
end

go
declare @PageCount int
exec usp_test2 5,2,@PageCount output

或者这样output 在调用sql语句怎么写
string sql="exec usp_test2 5,2,@PageCount output";

这样可以吗